I have a table called Hyp with below data
Quarter Target Date
Q1 50 Dec 31,2023
Q2 65 Mar 30,2024
Q3 90 June 30, 2024
Q4 120 Sep 30, 2024
Basically the targets are incremental counts and trying to derive like below:
Quarter Target
Q1 50
Q2 15
Q3 25
Q4 30
Q2 target will be Q2 Target - Q1 target
Wrote a DAX below but it is returning blank:
prevQtr = CALCULATE(SUM('Hyp'[taregt]), PREVIOUSQUARTER('Hyp'[Date]))
So first I created a calculated column for the quarter to get the number:
QuarterNumber =
SWITCH(
'Hyp'[Quarter],
"Q1", 1,
"Q2", 2,
"Q3", 3,
"Q4", 4,
BLANK()
)
Then I created another calculated column for the incremental target :
Incremental Target =
VAR CurrentQuarterTarget = 'Hyp'[Target]
VAR CurrentQuarterNumber = 'Hyp'[QuarterNumber]
VAR PreviousQuarterTarget =
CALCULATE(
MAX('Hyp'[Target]),
FILTER(
'Hyp',
'Hyp'[QuarterNumber] = CurrentQuarterNumber - 1
)
)
RETURN
IF(
ISBLANK(PreviousQuarterTarget),
CurrentQuarterTarget,
CurrentQuarterTarget - PreviousQuarterTarget
)
So I try to get the target value and the quarter number of the current row. so I can calculate the target value of the previous quarter and check if there is no previous quarter and return the current target. Otherwise, I count the difference between the current target and the previous quarter's target.