cancel
Showing results for
Did you mean:
Frequent Visitor

## Find the difference between colums in matrix

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.

3 REPLIES 3
Super User IV

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Solution Sage

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``````

Super User III

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

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.