Forum Discussion
Helper IIDifference between two years - how to calculate?
Hello
I have the data organised in the following way:
Year Department Team Expenses Total budget
2015 A CVB 678 800
2014 before cut A POK 908 1009
2014 after cut B BNM 800 990
I would like to be able to calculate:
Year difference (for example 2015 minus 2014 after cut) for expenses and then for the total (I imagine these will be two separate measures)
I hope the calculation will work when I filter team or department.
I would like to be able to show in the table or a card a difference.
Any ideas? I am new to DAX and don't know where to start
Thanks
Ewa
For the data as shown I would calculate the total expenses first with these measures
2015 Expenses = CALCULATE(SUM(Table1[Expenses]),'Table1'[Year]="2015")
2014 Expenses after cut = CALCULATE(SUM(Table1[Expenses]),'Table1'[Year]="2014 after cut")
The difference measure would then be
2015 less 2014 ac = [2015 Expenses]-[2014 Expenses after cut]
and for only department A
A only = CALCULATE([2015 less 2014 ac],FILTER('Table1',[Department]="A"))
8 Replies
MarkSResolver IV
For the data as shown I would calculate the total expenses first with these measures
2015 Expenses = CALCULATE(SUM(Table1[Expenses]),'Table1'[Year]="2015")
2014 Expenses after cut = CALCULATE(SUM(Table1[Expenses]),'Table1'[Year]="2014 after cut")
The difference measure would then be
2015 less 2014 ac = [2015 Expenses]-[2014 Expenses after cut]
and for only department A
A only = CALCULATE([2015 less 2014 ac],FILTER('Table1',[Department]="A"))
ewuchatkaAnyone? :) Please help!
- v-huizhn-msft
Microsoft Employee
Hi ewuchatka,
You want to create a measure to calculate 678-800=-122, right? Then you calculate total for what? Based on your description, I am unable to reproduce your scenario. Please post more years' data and list expected result, so I can create DAX formulas clearly.
Best Regards,
Angelia- ewuchatka
Thanks v-huizhn-msft
What I am trying to do is (I think and I hope) simple :). Sum of something minut Sum of something else. Sorry, my use of the total was probably confusing.
I would like to be able to build two calculations:
1. General calculation:
The total(sum) of 2015 (Expenses) MINUS the total (sum) of 2014 after cut (Expenses).
When you look at the below data, this should be 1640 - 1460 = 180 (when data not filtered) . However, hope that this calculation will work when I use a slicer and I, for example filter (slicer) on the department.
2. Create a calculation so that is specific to the Department so it always calculates a department.
The total(sum) of 2015 (Expenses) of (Department A) MINUS the total (sum) of 2014 after cut (Expenses) of (Department A).
820-680=140
I hope this makes sense now. Please let me know if you have any questions.
Year
Department Team Expenses Total budget 2015 A CBV 600 1000 2014 after cut A CBV 500 900 2014 before cut A CBV 650 850 2015 A RFG 220 300 2014 after cut A FG 180 250 2014 before cut A YHB 200 280 2015 B MNB 820 1200 2014 after cut B LOP 780 1010 2014 before cut B PLK 800 1000 GilbertQ, thanks for your help. I am not entirely sure if I understand your approah as I am quite new to Power BI - what is the data table What is the rationale? Can youc advise if this helps with the above?
- GilbertQ
Super User
Hi ewuchatka
Apologies for not knowing that you are still new to Power BI.
Here is a great blog post by Reza Rad explaining why having a date table is good practise and how to leverage it?
http://radacad.com/secret-of-time-intelligence-functions-in-power-bi
I hope that this helps.
- GilbertQ
Hi ewuchatka,
I would first suggest that you create a date table, and map your data to the date table. This will make it a lot easier to do date calculations in DAX.
You can use this blog post do create the date table Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query
Then I would suggest you seperate out the "Before Cut" and "After Cut" into a new column.
What that will do, is then allow you to create a calculated measure with a filter based on the details above.
EG:
Before Cut = CALCULATE(sum('TableName'[Expenses]),Filter('TableName','TableName'[ColumnName] = "Before Cut"))And you could then do the same for the "After Cut"
In terms of doing the Year Calculations you can reference the following links for more information:
