Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Future Revenue and Expense Projections DAX Formula Help

Hello,

 

I am trying to add a future projections column next to my Actuals.  What I want to capture are just to do projections for the months that have $0 or less for and if they have any amounts higher than 0 the forecast for that given month would be blank.  For example on my table right now since November and December are $0 the column with projections would have projected amounts for November and December but for Jan - Oct. the projections would be blank.  

 

The projection method I would like to do is take the average change from 2021 actuals vs. 2020 Actuals and multiply that number for the months with 0 or less rev/exp.

 

An example if it were excel all would be using various If statements but I cannot for the life of me figure out Dax i attached a picture of what I need:

The kicker is that I have no idea how once we get July in there the new average is automatically calcualted using 7 months as opposed to 6.

 

My two data sources are basic as it is just 2021 Actuals and 2020 Actuals.


Thanks in advance and any clarification Id be happy to do.

 

Formula Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      You are the GOAT thank you!  One more quesion how do uou get the projection measure to sum at very end?  

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your feedback.

        Please try to change the last measure like below.

         

         

        Projection measure : =
        VAR monthlychangeavg =
        CALCULATE ( [Percent Change avg. :], REMOVEFILTERS ( 'Month' ) )
        RETURN
        SUMX (
        VALUES ( 'Month'[Month] ),
        CALCULATE ( IF ( [Sales 2021 :] = 0, [Sales 2020 :] * monthlychangeavg ) )
        )