cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnnJ2209
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.

 

 

AnnJ2209_1-1606229126172.png

 

 

 

3 REPLIES 3
amitchandak
Super User IV
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!

daxer
Solution Sage
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

 

 

edhans
Super User III
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

Helpful resources

Announcements
User Groups Public Preview

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.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors