Search code examples
powerbidaxdata-analysisbusiness-intelligence

PowerBI DAX Compare the difference between values based on ID and Date using slicers

I am using a DAX formula that isn't quite working and I'm wondering if I'm going about it the wrong way. I need to be able to use slicers to select a Name and two dates and compare the difference in Amounts per date.

Table


Difference =
VAR amnt =
    MAX ( 'Table'[Amount] )
VAR id =
    MAX ( 'Table'[Id] )
VAR _currentlyVisibleDate =
    MAX ( 'Table'[Date] )
VAR _immediatelyPrecedingDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < _currentlyVisibleDate )
RETURN
    amnt
        - CALCULATE (
            MAX ( 'Table'[Amount] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Id] = _id
                    && 'Table'[Date] = _immediatelyPrecedingDate
           )
       )
Solution

  • First create a separate Table with single column of distinct Names

    Table = DISTINCT(Data[Name])

    Give relationship to main table. Select this name column in one slicer. Another slicer, select date column from main table.

    Write a measure to calculate the difference between amount when two dates are selected.

    Measure = if(
COUNTROWS(ALLSELECTED(Data[Date])) <> 2, BLANK(), CALCULATE(SUM(Data[Amount]), FILTER(data, Data[Date] = MAX(Data[Date]))) -  CALCULATE(SUM(Data[Amount]), FILTER(data, Data[Date] = MIN(Data[Date]))))

    enter image description here