Forum Discussion

Anon2020's avatar
Anon2020
Helper 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 filter. I have tried several variations and looked through multiple forums only to find solutions that are not dynamic. Any help is greatly appreciated!!!

 

Here is what I have so far 

 

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.

     

6 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    Hello Anon2020 

     

     

    Base Measure

     

    Total Sales =
    SUM ( Sales[SalesAmount] )

     

    Selected Years

     

    Selected Year Min =
    MIN ( 'Date'[FiscalYear] )

    Selected Year Max =
    MAX ( 'Date'[FiscalYear] )

     

    Sales for Min Year

     

    Sales Min Year =
    CALCULATE (
    [Total Sales],
    'Date'[FiscalYear] = [Selected Year Min]
    )

     

    Sales for Max Year

     

    Sales Max Year =
    CALCULATE (
    [Total Sales],
    'Date'[FiscalYear] = [Selected Year Max]
    )

     

    Year Difference (Final Measure)

     

    Year Difference =
    IF (
    DISTINCTCOUNT ( 'Date'[FiscalYear] ) = 2,
    [Sales Max Year] - [Sales Min Year],
    BLANK()
    )


    I

    If my response helped you, please consider clicking
    Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


    Thanks,


    Connect with me on:

    LinkedIn
  • danextian's avatar
    danextian
    Super User
    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
    Community 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

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months ago

    Hi Anon2020 ,

    Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months ago

    Hi Anon2020 ,
    Since we didnt hear back from you, we will be closing this thread.
    If you have any issues, please feel free to raise a separate ticket and we will be happy to assist you.

    Thank You