Find the difference between colums in matrix

[AnnJ2209]

Hello,

I am trying to find the differense between colums in each year - i want it to show/meassure the differense between DG in 2019 and 2020. 

Any ideas how I do that - also the colums DG is measured collums.

[amitchandak]

@AnnJ2209 , Time intelligence should help. If you have date table the use TI, else have a separate date table 

With date and date and date table 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

diff = [This year Sales] - [Last year Sales ]

With Date/year table. No need of TI

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Building on @edhans's example, here's one that responds to any selection of dates, not only years. It can compare months against the previous year's months and the likes.

[DG Diff] =
VAR __prior =
    CALCULATE(
        [YourDGMeasure]
        SAMEPERIODLASTYEAR( 'Date'[Date] )
    )
VAR __current = [YourDGMeasure]
VAR __diff = __current - __prior
RETURN
    __diff

[edhans]

Hard to help without some sample data and knowing the contents of your measure, but the logic would be along the lines of this:

MeasureName =
VAR varPriorYear =
    CALCULATE(
        [YourDGMeasure],
        FILTER(
            ALL( 'Date'[Year] ),
            'Date'[Year] = 2019
        )
    )
VAR varCurrentYear =
    CALCULATE(
        [YourDGMeasure],
        FILTER(
            ALL( 'Date'[Year] ),
            'Date'[Year] = 2020
        )
    )
VAR Result = varCurrentYear - varPriorYear
RETURN
    Result

Of course you could dynamically calculate current and prior year vs the hardcoded 2019/2020 above.

If that doesn't help, provide some sample data per links below and the contents of your DG measure.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum