Forum Discussion

Benedict21's avatar
Benedict21
Frequent Visitor
3 years ago

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

  • Benedict21 , Try a measure like using Incidents Forecast measure

     

    calculate(lastnonblankvalues(Date[Date], [Incidents Forecast]), filter(all(Date), Date[Date] <= max(date[Date]) )

    • Benedict21's avatar
      Benedict21
      Frequent Visitor

      Hi, 

       

      Thank you, but the results are 0s and 1s.

      • Benedict21's avatar
        Benedict21
        Frequent 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])))