Dax Measure

[ronaldbalza2023]

Hi everyone, would like to have a DAX measure in the following scenario below. Appreciated your help on this in advance 🙂

Problem statement: Basically, I am tracking if the target revenue is met every quarter. I have a measure with the name of Revenue and Target. So, any shortfall from a prior quarter (if the Revenue is less than the Target), the shortfall amount will be carried forward and applied to the current quarter.

Let's say in Q1, I have revenue of 3000 and a target of 5000. Come next Q2 (basically, the target is not met), the shortfall amount in Q1 is 2000 (5000 - 3000), it will be then the starting revenue of Q2.

Hope this makes sense 🙂. Attached is the Sample Data 

[v-angzheng-msft · Accepted solution]

There, @ronaldbalza2023

It looks like you have the solution in another thread you posted.

IF revenue > target in Q1

THEN Revenue Q2 = Revenue

ELSE revenue < target in Q1

THEN Revenue Q2 = Revenue minus Target

Assuming you have only selected 1 year:

Projected revenue =

VAR _SelectedQuarter = SELECTEDVALUE(DimDate[Quarter])

RETURN

IF( [Revenue] > [Target] , [Revenue], [Revenue] - [Target] )

if this question has been resolved consider marking this thread as answered due to thread duplication

https://community.powerbi.com/t5/Desktop/DAX-Measure/m-p/2331876

Best regards
Community Support Team _ Zeon Zheng

If this post helps, consider Accepting it as the solution to help other members find it more quickly.

[parry2k]

@ronaldbalza2023 what is your expected output?

[ronaldbalza2023]

Hi @parry2k , thanks for taking the time on this. The output is dynamic depending on whether the target revenue is met or not. If the target is met, then the revenue of Q2 will be the starting point, if the target revenue is not met, then whatever the shortfall amount of the previous quarter will be the starting point of the revenue in the next quarter. Thanks 🙂