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 ,
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.
Hello v-yinliw-msft,
Sorry to bother you but i tried to implement the calculations. but i got one value that isnt correct.
for example, here i should get the value of 30 June as the value date of April is before the 30-June.
Here the pbix: https://drive.google.com/file/d/10c9yBPxfNfUpWG-UisERDaI67WAOIixm/view?usp=sharing
Thanks again
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.