MovingAverage.csx

#r "Microsoft.VisualBasic"

using Microsoft.VisualBasic;
using System;
using System.Windows.Forms;

string stringInterval =
    Interaction.InputBox(
        Prompt: "Enter the interval for the moving average:",
        Title: "Interval",
        DefaultResponse: "14"
    );
if(stringInterval== "") {
    Error("No interval provided");
    return;
}

string displayFolder =
    Interaction.InputBox(
        Prompt: "What display folder should these measures be in?",
        Title: "Display Folder",
        DefaultResponse: "Averages"
    );
if(stringInterval== "") {
    Error("No interval provided");
    return;
}

int interval = int.Parse(stringInterval);

// List of all DateTime columns in the model
var _dateColumns = Model.AllColumns.Where(c => c.DataType == DataType.DateTime ).ToList();


// Create a new form
Form form = new Form();
form.Text = "Select an period";
form.Width = 300;
form.Height = 150;
form.StartPosition = FormStartPosition.CenterScreen;

// Create a ComboBox and add items
ComboBox comboBox = new ComboBox();
comboBox.Items.AddRange(new string[] { "DAY", "MONTH", "QUARTER", "YEAR" });
comboBox.Location = new System.Drawing.Point(50, 20);
comboBox.Width = 200;

// Create an OK button
Button okButton = new Button();
okButton.Text = "OK";
okButton.Location = new System.Drawing.Point(50, 60);
okButton.DialogResult = DialogResult.OK;

// Add the ComboBox and OK button to the form
form.Controls.Add(comboBox);
form.Controls.Add(okButton);

string selectedPeriod = "";

// Show the form as a dialog
if (form.ShowDialog() == DialogResult.OK)
{
    // Store the selected item in a variable
    selectedPeriod = comboBox.SelectedItem.ToString();
}

if(selectedPeriod== "") {
    Error("No period provided");
    return;
}


// Select the column with the earliest date in the model
try
{
    string _date = 
        SelectColumn(
            _dateColumns, 
            null, 
            "Select the date column for the calculation:"
        ).DaxObjectFullName;
    


        // Creates a SUM measure for every currently selected column and hide the column.
        foreach(var c in Selected.Measures)
        {
            var newMeasure = c.Table.AddMeasure(
                "Moving Average of " + c.Name + " - " + selectedPeriod,  // Name
                "AVERAGEX( DATESINPERIOD( " + _date + ", LASTDATE( " + _date + " ), -" + interval + ", " + selectedPeriod +" ), " + c.DaxObjectFullName + " )",    // DAX expression
                displayFolder         // Display Folder
            );
            
            // Set the format string on the new measure:
            newMeasure.FormatString = "0.00";

            // Provide some documentation:
            newMeasure.Description = "This measure is the moving average of " + c.DaxObjectFullName;

            newMeasure.FormatDax();

     

        }
}
catch
{
    Error( "Date column not selected!" );
}