Forum Discussion
Using Dax calculate the difference between two values comparing two dates
Hi unkCandyd ,
You can try this method:
New two columns:
Time = VAR _min1 = CALCULATE ( MIN ( 'Targets'[target date] ), FILTER ( 'Targets', 'Targets'[id] = 1 ) ) VAR _min3 = CALCULATE ( MIN ( 'Targets'[target date] ), FILTER ( 'Targets', 'Targets'[id] = 3 ) ) VAR _max1 = CALCULATE ( MAX ( 'Targets'[target date] ), FILTER ( 'Targets', 'Targets'[id] = 1 ) ) VAR _max3 = CALCULATE ( MAX ( 'Targets'[target date] ), FILTER ( 'Targets', 'Targets'[id] = 3 ) ) RETURN SWITCH ( TRUE (), 'actual'[Actual date] > _min1 && 'actual'[Actual date] <= _max1 && 'actual'[Id] = 1, _max1, 'actual'[Actual date] <= _min1 && 'actual'[Id] = 1, _min1, 'actual'[Actual date] > _min3 && 'actual'[Actual date] <= _max3 && 'actual'[Id] = 3, _max3, 'actual'[Actual date] <= _min3 && 'actual'[Id] = 3, _min3 )Target Value = CALCULATE(MAX(Targets[target value]), FILTER('Targets', 'Targets'[target date] = 'actual'[Time] && 'actual'[Id] = 'Targets'[id]))Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi unkCandyd,
You can try this method:
New a measure:
DateDiff =
CALCULATE (
DATEDIFF ( MIN ( 'Targets'[target date] ), MAX ( 'Targets'[target date] ), DAY ),
FILTER ( 'Targets', 'Targets'[id] )
)
Then new some columns:
MidDate =
CALCULATE (
MIN ( 'Targets'[target date] ) + [DateDiff] / 2,
FILTER ( 'Targets', 'actual'[Id] = 'Targets'[id] )
)
NeedDate =
IF (
'actual'[MidDate] > [Actual date],
CALCULATE (
MIN ( 'Targets'[target date] ),
FILTER ( 'Targets', 'Targets'[id] = 'actual'[Id] )
),
CALCULATE (
MAX ( 'Targets'[target date] ),
FILTER ( 'Targets', 'Targets'[id] = 'actual'[Id] )
)
)
Target Value =
CALCULATE (
SUM ( Targets[target value] ),
FILTER ( 'Targets', 'Targets'[target date] = 'actual'[NeedDate] )
)
Variance = [Actual value] - [Target Value]The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello thanks for your reply, here for the date of 18-May, the target value should be 100 as the date has already passed the 31 March:
Maybe I explained wrongly so after the date is passed we should get the new target of the next date
Hi unkCandyd ,
Understood.
But i am a little confused, the 9/30/2022 and 12/15/2022 are both passed the 8/30/2022, and it used the target 8/30. Could you please explain the logic more to me?
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, Thank you for your reply. let me explain it more clearly.
For example:
- I have the target dates are 6-June with a target value of 100 and 31-Dec with a target value of 200,
- and the actual date is 31-Aug, so the target date corresponding is 31-Dec.
So getting the target value according to the actual date and target date comparison
Again, I may have explained wrongly,
I have succeeded to implement it in excel by using the match formula. but using Dax I am blocked..Thank you again
Hi, unkCandyd ,
Please fix this:
Change the column NeedDate like this:
NeedDate = IF ( 'actual'[MidDate] > [Actual date] && 'actual'[Actual date] < MIN ( 'Targets'[target date] ), CALCULATE ( MIN ( 'Targets'[target date] ), FILTER ( 'Targets', 'Targets'[id] = 'actual'[Id] ) ), CALCULATE ( MAX ( 'Targets'[target date] ), FILTER ( 'Targets', 'Targets'[id] = 'actual'[Id] ) ) )The result is:
Hope this helps you. Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.