Compare years values by filter

[MiguelMercadoS]

Hello everyone

I need to compare values by filter, for example 

I have in 2019 a expenses for 15000000 

for 2018 expenses for 20000000

for 2017 35000000

and for 2016 45000000

if i filter two years, for example 2019 and 2018 i need to calculate the diference wich is 500000 but if i would like to compare 2018 and 2017 the value would be 15000000 and the same if i need to compare values by the year filtered. 

I have this in Tableau wich works: 

ZN(SUM([-Ing Directos])) - LOOKUP(ZN(SUM([-Ing Directos])), -1)

But I don't know how to do that in power bi, can yopu please help me? thank you in advance!

[dax · Accepted solution]

Hi @MiguelMercadoS , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[Syndicate_Admin]

Thank you Zoe, it's just what I needed. Best regards

[Ashish_Mathur]

Hi,

Create a Calendar Table with Year as a calculated column.  Build a relationship from the Date column of your source data table to the date column of yoru Calendar Table.  In your slicer, drag Year from the Calendar Table and select any 2 years.  Write these measures:

Total = SUM([-Ing Directos])

Total in last selected year = calculate([Total],datesbetween(Calendar[Date],edate(max(calendar[Date]),-12)+1,max(calendar[Date])))

Total in last selected year = calculate([Total],datesbetween(Calendar[Date],min(calendar[Date]),edate(min(calendar[Date]),12)-1))

Difference = [Total in last selected year]-[Total in last selected year]

Hope this helps.

[Greg_Deckler]

@MiguelMercadoS - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.