Forum Discussion

ewuchatka's avatar
ewuchatka
Icon for Helper II rankHelper II
9 years ago
Solved

Difference between two years - how to calculate?

Hello   I have the data organised in the following way:   Year                      Department      Team          Expenses       Total budget 2015                      A                       CV...
Need Help
  • MarkS's avatar
    MarkS
    9 years ago

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

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

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:

 

DAX Patterns - Time Intelligence