Forum Discussion
Dax Measure
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
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 ZhengIf this post helps, consider Accepting it as the solution to help other members find it more quickly.
3 Replies
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 ZhengIf this post helps, consider Accepting it as the solution to help other members find it more quickly.
ronaldbalza2023 what is your expected output?
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 🙂
