Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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

1 Reply