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

     

     

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