topic Re: Using Dax calculate the difference between two values comparing two dates in DAX Commands and Tips

Using Dax calculate the difference between two values comparing two dates

unkCandyd — Wed, 16 Nov 2022 09:49:08 GMT

Hello,

Iam beginner in power bi I would like your help.

I have two data sets.

the first one presents the actual values:

the second one is the target values

I want to calculate actual- target values as the target can be retreived from the target table according to actual date. For example:

For id=1, first row I have the date 31-Jan so I should the target value of the date 31-march

My result will look like as:

I want to implement it in dax but i dont know how to retrieve the target value according to actual date.

I hope you can help me to do that,

thanks.

Re: Using Dax calculate the difference between two values comparing two dates

Greg_Deckler — Tue, 15 Nov 2022 14:40:33 GMT

unkCandyd Try:

Target Value Measure = VAR __ActualDate = MAX('Actuals'[Actual date]) VAR __Targets = ADDCOLUMNS( 'Targets', "__DaysAway",ABS( ([target date] - __ActualDate) * 1.) ) VAR __Min = MINX(__Targets,[__DaysAway]) VAR __Result = MINX(FILTER(__Targets, [__DaysAway] = __Min),[target value]) RETURN __Result

Re: Using Dax calculate the difference between two values comparing two dates

unkCandyd — Wed, 16 Nov 2022 08:52:07 GMT

Hello, Thank you for reply. bu I dont want to compare the days away, as it may give the wrong target value.

For example:

I have the target dates are 6-June with a target value 100 and 31-Dec with target value 200,
and the actual date is 31-Aug, so the target value for this date is 200. but using the suggested calculation it will give me 100. as it is comparing the days away

Again, I may have explained wrongly, what I want to be able to compare the two dates, so if the actual date <= target date then var = actual value- target value, also to compare all dates.
I have succeeded to implement it in excel by using the match formula. but using Dax I am blocked..

Thank you again

Re: Using Dax calculate the difference between two values comparing two dates

v-yinliw-msft — Wed, 16 Nov 2022 09:05:33 GMT

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.

Re: Using Dax calculate the difference between two values comparing two dates

unkCandyd — Wed, 16 Nov 2022 09:31:12 GMT

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

Re: Using Dax calculate the difference between two values comparing two dates

v-yinliw-msft — Wed, 16 Nov 2022 09:39:24 GMT

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.

Re: Using Dax calculate the difference between two values comparing two dates

unkCandyd — Wed, 16 Nov 2022 09:44:56 GMT

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

Re: Using Dax calculate the difference between two values comparing two dates

v-yinliw-msft — Wed, 16 Nov 2022 09:57:27 GMT

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.

Re: Using Dax calculate the difference between two values comparing two dates

unkCandyd — Wed, 16 Nov 2022 10:33:48 GMT

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

Re: Using Dax calculate the difference between two values comparing two dates

v-yinliw-msft — Thu, 17 Nov 2022 09:49:32 GMT

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.