% difference

[Anonymous]

Hello Gang,

Once again i need your help. I went through differents subjects but i couldn't find any suitable solution for my issue. 

I am trying to create a measure to calculate the difference between two Fiscal years performance (Performance X), here is my measure:

Difference = CALCULATE(Table[Performance X],'SHIP DATE'[Fiscalyear]=2021)-CALCULATE(Table[Performance X],'SHIP DATE'[Fiscalyear]=2020)

The measure give the good result but here is my issue, I would like to have this matrix:

	2020	2021
Division	Performance X	Performance X	Difference
1	50%	65%	15%
2	50%	45%	-5%

But when I put my measure into Values I have this:

	2020		2021
Division	Performance X	Difference	Performance X	Difference
1	50%	15%	65%	15%
2	50%	-5%	45%	-5%

What can I do to have only the Difference shown once?

Second question regarding the measure; Would it be possible to have a dynamic Previous Fiscal Year and Current Fiscal Year filter?

Best regards,

Marc 

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

I build a table like yours to have a test.

Build a measure:

Difference =
VAR _Currnt =
    MAX ( 'Table'[FiscalYear] )
VAR _Previous = _Currnt - 1
VAR _Cp =
    SUM ( 'Table'[Performancex] )
VAR _PP =
    CALCULATE (
        SUM ( 'Table'[Performancex] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Division] = MAX ( 'Table'[Division] )
                && 'Table'[FiscalYear] = _Previous
        )
    )
RETURN
    IF ( ISBLANK ( _PP ), BLANK (), _Cp - _PP )

Result:

 

In matrix the measure will show the result in columns for each year.

You can download the pbix file from this link:  % difference

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Anonymous , you can use time intelligence or this year vs last year

refer Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

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))
//Only year vs Year, not a level below

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[nandukrishnavs]

@Anonymous 

Try something like this.

Measure

Diff = 
VAR _currentYear =
    SELECTEDVALUE ( LISC[Fiscalyear] )
VAR _previouYear = _currentYear - 1
VAR _currentVal =
    CALCULATE ( SUM ( LISC[LISC %] ), LISC[Fiscalyear] = _currentYear )
VAR _previousVal =
    CALCULATE (
        SUM ( LISC[LISC %] ),
        REMOVEFILTERS ( LISC[Fiscalyear] ),
        LISC[Fiscalyear] = _previouYear
    )
VAR _diff = _currentVal - _previousVal
RETURN
    _diff

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Greg_Deckler]

@Anonymous - I created this quick measure to solve that problem:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428