Forum Discussion

unkCandyd's avatar
unkCandyd
Frequent Visitor
3 years ago
Solved

Using Dax calculate the difference between two values comparing two dates

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.

DAX
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    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.

     

     

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      unkCandyd
      Frequent Visitor
      3 years ago

      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

       

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Community Support
    3 years ago

    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's avatar
      unkCandyd
      Frequent Visitor
      3 years ago

      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's avatar
        v-yinliw-msft
        Community Support
        3 years ago

        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.