Dynamic Column Difference in Matrix

[Anon2020]

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 

[v-sdhruv]

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]

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 @pankajnamekar25  , @danextian  and @Shahid12523  for your valuable inputs to the query.

[Shahid12523]

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

[danextian]

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]

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