Help in creating Data Delay Period Measure and Visualization T+1, T+2, T+3!!!!

[Jared__]

Hi Experts, i need help in creating a visual similar to the one below.

However, for Column 6 i've created a custom table and used a Switch function To bring in the Total Sales. The issue is the relationships does not seem to be flowing through. Can someone help on this?

For notes, i've included the measure i'm current using.

-------------------------
-- Measure: [DelayPeriod]
-------------------------
MEASURE All_Measures[DelayPeriod] = 
    VAR SelectedPeriod = SELECTEDVALUE('Delayed Period'[Period])
    VAR StartMonth =
        SWITCH(
            SelectedPeriod,
            "T+1", MIN(DimDate[Date]),
            "T+2", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 1,
            "T+3", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 2,
            "T+4", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 3,
            "T+5", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 4,
            "T+6", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 5,
            "T+7", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 6,
            "T+8", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 7,
            "T+9", DATE(YEAR(EOMONTH(MIN(DimDate[Date]), 0)), MONTH(EOMONTH(MIN(DimDate[Date]), 0)), 1) + 8,
            BLANK()
        )
    VAR EndMonth = MAX(DimDate[Date])
    VAR DateFilter = FILTER(DimDate, DimDate[Date] >= StartMonth && DimDate[Date] <= EndMonth)
    
    RETURN
        SWITCH(
            TRUE(),
            SELECTEDVALUE(Custom_Visual_Card[Grouping]) = "Volume",
            SWITCH(
                TRUE(),
                SelectedPeriod = "T+1",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+2",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+3",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+4",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+5",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+6",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+7",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+8",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                SelectedPeriod = "T+9",
                CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Volume]), DateFilter),
                BLANK()
            ),
            SWITCH(
                TRUE(),
                SELECTEDVALUE(Custom_Visual_Card[Grouping]) = "Revenue",
                SWITCH(
                    TRUE(),
                    SelectedPeriod = "T+1",
                    CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Revenue]), DateFilter),
                    SelectedPeriod = "T+2",
                    CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Revenue]), DateFilter),
                    SelectedPeriod = "T+3",
                    CALCULATE(AVERAGEX((_2__Transformed_Forecast), _2__Transformed_Forecast[MSE Revenue]), DateFilter),
                    BLANK()
                )
            )
        )
    DisplayFolder = "Forecasting_UC"

BR

Adrian

[amitchandak]

@Jared__ , for moving avg, Create a date using month year and join with date table and measure like

example

Cumm Sales = CALCULATE(AverageX(Values('Date'[Month Year]), CALCULATE(SUM(Sales[Sales Amount]))),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc