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 ,
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.