Forum Discussion
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
- amitchandak
Super User
Anonymous , 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=42Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc