Forum Discussion

Anon2020's avatar
Anon2020
Icon for Helper I rankHelper I
1 year ago
Solved

Dynamic Column Difference in Matrix

I have a matrix set up with Total sales by FY by Producyt Code. I am unable to find a formula that accurately depicts the difference betyween the two selected years that the user chooses from a filte...
Need Help
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    Hi Anon2020 

    It seems to me that you're trying to show the min and max selected years and a column for the difference, if so try this DAX measure

    Min Max Difference = 
VAR _minYr =
    MINX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] )
VAR _maxYr =
    MAXX ( ALLSELECTED ( CalendarTable ), CalendarTable[Year] )
VAR _years = { _minYr, _maxYr }
VAR _minYrValue =
    SUMX (
        FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _minYr ),
        [Total Sales]
    )
VAR _maxYrValue =
    SUMX (
        FILTER ( ALLSELECTED ( CalendarTable[Year] ), CalendarTable[Year] = _maxYr ),
        [Total Sales]
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ( HASONEVALUE ( CalendarTable[Year] ) ), _maxYrValue - _minYrValue,
        SELECTEDVALUE ( CalendarTable[Year] )
            IN _years && HASONEVALUE ( CalendarTable[Year] ), [Total Sales]
    )

    Please see the attached pbix.

     

Shahid12523's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
1 year ago

Use this DAX measure to calculate the dynamic difference between two selected FYs


Sales Difference Between FYs =
VAR SelectedFYs = VALUES('Date'[FinYear])
VAR FY1 = MIN(SelectedFYs)
VAR FY2 = MAX(SelectedFYs)
RETURN
IF(
COUNTROWS(SelectedFYs) = 2,
CALCULATE([Total Sales], 'Date'[FinYear] = FY2) -
CALCULATE([Total Sales], 'Date'[FinYear] = FY1)
)

 

Replace 'Date'[FinYear] with your Fiscal Year column
Replace [Total Sales] with your measure