Forum Discussion

Anon2020's avatar
Anon2020
Helper I
11 months 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
    11 months 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.

     

pankajnamekar25's avatar
pankajnamekar25
Super User
11 months 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