Difference between two years - how to calculate?

[ewuchatka]

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

[MarkS · Accepted solution]

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"))

[ewuchatka]

Thank you @MarkS!! This is what I need 🙂

[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 Quer...

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:

DAX Patterns - Time Intelligence

[v-huizhn-msft]

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]

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.

[ewuchatka]

Hello

Thanks @GilbertQ

It looks interesting and certainly I will have a read. Power BI/DAX are very new to me so I haven't explored much of it but I'm looking forward to this. It's not clear to me how this could help me with what I am trying to do.

Kind regards

Ewa

[ewuchatka]

Anyone? 🙂 Please help!