Forum Discussion
DAX repeating values
Hello,
I am creating a forecast by averaging the previous 6 months data. I can get those values no problem. However, I want to repeat next month's forecast for the upcoming months. For example, I would like December 2022 value to repeat for January 2023, February 2023, etc. I tried taking the MAX value from the Incidents Forecast measure, but you cannot use that function on a measure. Any suggestions on how? Thank you!
DAX measures:
Incidents Forecast =
VAR IncLM = CALCULATE([Incidents Average per Year Month], DATEADD(Dates[Date], -1, MONTH))
VAR Inc2M = CALCULATE([Incidents Average per Year Month], DATEADD(Dates[Date], -2, MONTH))
VAR Inc3M = CALCULATE([Incidents Average per Year Month], DATEADD(Dates[Date], -3, MONTH))
VAR Inc4M = CALCULATE([Incidents Average per Year Month], DATEADD(Dates[Date], -4, MONTH))
VAR Inc5M = CALCULATE([Incidents Average per Year Month], DATEADD(Dates[Date], -5, MONTH))
VAR Inc6M = CALCULATE([Incidents Average per Year Month], DATEADD(Dates[Date], -6, MONTH))
Return
DIVIDE((IncLM+Inc2M+Inc3M+Inc4M+Inc5M+Inc6M+), 6)
Cumulative Forecast =
IF(ISBLANK([Incidents Average per Year Month]), [Incidents Forecast], [Incidents Average per Year Month])
6 Replies
- amitchandakSuper User
Benedict21 , Try a measure like using Incidents Forecast measure
calculate(lastnonblankvalues(Date[Date], [Incidents Forecast]), filter(all(Date), Date[Date] <= max(date[Date]) )
- Benedict21Frequent Visitor
Hi,
Thank you, but the results are 0s and 1s.
- Benedict21Frequent Visitor
I tried using this measure; however, it is not providing the correct result.
Test =
CALCULATE(LASTNOTBLANKVALUE('Dates'[Date], [Incidents Forecast]), FILTER(ALL('Dates'[Date], [Date] <=MAX('Dates'[Date])))