cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## loop through a column using a condition to create a measure

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

1 ACCEPTED SOLUTION
Super User

see attached

4 REPLIES 4
Super User

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.

Helper IV

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.

Super User

see attached

Helper IV

@lbendlin thank you so much! exactly what I wanted

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.