Future Revenue and Expense Projections DAX Formula Help

[Anonymous]

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.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

[Anonymous]

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

[Jihwan_Kim]

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 ) )
)