loop through a column using a condition to create a measure

[jessicarocha]

Hello, 

I need help with a conditional measure. 

My dataset contains:

- actual revenue in a monthly basis

- plan revenue values. The plan is per quartile. We receive an average monthly value for the given quartile

- historization for the plan, since it is updated every two months

I need to create a measure that gives the total planned revenue for the current fiscal year. 

In cases where there are no actual values in the current business year, it is simple. I can just multiple the "average monthly plan value per quartile" by 3 (since it is the average value per quartile and in each quartile there are 3 months). 

The problem for me is to create a conditional formula for when in the quartile there are actual values as well. 

My idea would be to loop through the quartile. In a conceptual form, something like that:

if count([monthly values]) per [year quarter] = 1, then [monthly values]*1 + [average monthly plan value per quartile]*2

else if count([monthly values]) per [year quarter] = 2, then [monthly values]*2 + [average monthly plan value per quartile]*1

else if count([monthly values]) per [year quarter] = 3, then [monthly values]*3

else [average monthly plan value per quartile]* 3

In the example above, the result should be: 

137*1 + 156*2 = 449

449 + 438 + 369 + 306 = 1562 (instead of 1581)

Is this possible in dax? I created a dummy power bi to represent my problem in the link below. 

Thank you so much for the help!

DUMMY EXAMPLE in Power BI: Drive folder 

[lbendlin]

You may want to consider cleaning up your data model first. The fiscal calendar information needs to go into the "dim date" table and be removed from the fact table. The Calendar table needs to be marked as a Dates table.

[jessicarocha]

Hi @lbendlin,

thanks for the feedback. i do have the calendar table in a separated date dimension. I created a dummy file last friday and loaded in the sharepoint (link above). 

I still used the date column as a PK to connect to the fact table in the dummy example but in my real model it is just a random index PK.

[lbendlin · Accepted solution]

see attached

[jessicarocha]

@lbendlin thank you so much! exactly what I wanted