Using Dax calculate the difference between two values comparing two dates

[unkCandyd]

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.

[v-yinliw-msft]

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.

[unkCandyd]

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

[v-yinliw-msft]

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.

[unkCandyd]

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

[v-yinliw-msft]

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.

[unkCandyd]

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

[v-yinliw-msft · Accepted solution]

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.

[Greg_Deckler]

@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

[unkCandyd]

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