DAX measure

Hi,

Yes. Since you need the latest selected date value minus the older selected date value, you can keep your existing logic and create a separate variance measure.

Assuming Mapping[date_Table] is the date field used in the slicer and sales[Value] contains the value:

Variance =

VAR SelectedDates =

    VALUES ( Mapping[date_Table] )

 

VAR DateCount =

    COUNTROWS ( SelectedDates )

 

VAR OldDate =

    MINX ( SelectedDates, Mapping[date_Table] )

 

VAR LatestDate =

    MAXX ( SelectedDates, Mapping[date_Table] )

 

VAR OldValue =

    CALCULATE (

        SUM ( sales[Value] ),

        Mapping[date_Table] = OldDate

    )

 

VAR LatestValue =

    CALCULATE (

        SUM ( sales[Value] ),

        Mapping[date_Table] = LatestDate

    )

 

RETURN

    IF (

        DateCount = 2,

        LatestValue - OldValue,

        BLANK()

    )

Example

If the slicer has:

The measure returns:

130 - 100 = 30

If the selected dates are:

the measure returns blank, because exactly two dates must be selected.

If you want the variance to appear on each selected-date row

If your table visual has Mapping[date_Table] as a row, you can use:

Variance =

VAR SelectedDates =

    ALLSELECTED ( Mapping[date_Table] )

 

VAR DateCount =

    COUNTROWS ( SelectedDates )

 

VAR OldDate =

    MINX ( SelectedDates, Mapping[date_Table] )

 

VAR LatestDate =

    MAXX ( SelectedDates, Mapping[date_Table] )

 

VAR OldValue =

    CALCULATE (

        SUM ( sales[Value] ),

        REMOVEFILTERS ( Mapping[date_Table] ),

        Mapping[date_Table] = OldDate

    )

 

VAR LatestValue =

    CALCULATE (

        SUM ( sales[Value] ),

        REMOVEFILTERS ( Mapping[date_Table] ),

        Mapping[date_Table] = LatestDate

    )

 

RETURN

    IF (

        DateCount = 2,

        LatestValue - OldValue,

        BLANK()

    )

I recommend the second version if you are putting the measure into the same table visual where the selected dates are displayed, because it explicitly removes the current row's date filter before calculating the two comparison values.

Hope this helps.

Hi Explorer_​ ,

Based on the description if you want to have the calculation variance between two values you can try the following code:

Variance =   VAR _MaxDate = MAX(Mapping[date_Table] ) VAR _MinDate = MIN(Mapping[date_Table] )  RETURN CALCULATE( SUM ( sales[Value] ), Mapping[date_Table] =_MaxDate) - CALCULATE( SUM ( sales[Value] ), Mapping[date_Table] =_MinDate) 

This should give you the expected result if you select more than one value then it picks the Maximum and Minimum dates.

 

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: PBI Portugal 

Hi Explorer_​,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

Hi Explorer_​,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you