DynamicPreviousPeriods.cs

#r "Microsoft.VisualBasic"
using Microsoft.VisualBasic;
using System.Text.RegularExpressions;

// Select a measure
var offsetMeasure = Model.SelectMeasure();

// Select an offset column
string offsetColumn = SelectColumn(Model.Tables["Dates"].Columns.Where(
                                       col => col.Name.Contains("Offset")))
                          .Name;

// Get the interval
string stringInterval = Interaction.InputBox(
    Prompt: "Enter the interval for the offset calculation:", Title: "Interval",
    DefaultResponse: "1");
if (stringInterval == "") {
  Error("No interval provided");
  return;
}

// Build DAX string
string dax =
    @"
    VAR __PrevPer = MAX( Dates[" +
    offsetColumn + "]) -" + stringInterval +
    @"

    VAR __Result = CALCULATE ( [" +
    offsetMeasure.Name + @"], FILTER ( ALL ( Dates ), Dates[" + offsetColumn +
    @"] = __PrevPer))

    RETURN

    __Result
    ";

// Split by CamelCase
var words = Regex.Matches(offsetColumn, @"[A-Z][a-z]*")
                .Cast<Match>()
                .Select(m => m.Value)
                .ToList();

// Initialize variable
string period = "";

// Get the middle part (e.g., "Year", "Day", "Month")
if (words.Count == 3) {
  period = words[1];
}

// Build measure name
string measureName =
    "Prev " + stringInterval + " " + period + "(s) - " + offsetMeasure.Name;

// Add the measure
Model.Tables["1M"].AddMeasure(measureName, dax, "TI\\Offsets").FormatDax();