% of change since first month of the year using DAX and Power Pivot

Today we are solving a problem that I got from a Microsoft financial business users, she wanted to see the percentage change for every month of the fiscal year compared to the first month of that fiscal year. So lets go and see how we can solve this using DAX.

I have a model with two tables, one fact table and one data table. They are related through a date field in both tables.

The first thing we do here is create a PivotTable with Fiscal years and Fiscal months on rows. The fields are two columns in my date table:

Now adding the FyYear and FyMonth on rows and the the Sum of Revenue as values:

Now the first thing we need to do is get the sum of revenue for the first month of the fiscal year so that we can compare it with the value of the current month. This actually a combination of three pretty straightforward DAX expressions.

First we are need to be able to find the first day we have sales per year:

First date of the year=

CALCULATE(FIRSTNONBLANK(DateTable[Date]

,[Sum of Revenue])

,ALLEXCEPT(DateTable

,DateTable[FyYear]

)

)

This expression will get the first date in the datetable[date] where there is a value for [Sum of Revenue], then using CALCULATE we will make sure that it calculates that date for all the rows in the DateTable except keeping the filter on DateTable[FyYear]. In short: this will get the first date we have sales for each FiscalYear.

Testing this show it works (remember those are fiscal years on rows):

Now lets go an use this calculation in a calculated column to determine for each year what the first month is for that year:

=IF(
[Year]=YEAR([First date of the year])
&&
[Month]=MONTH([First date of the year])
,1
,0
)

This calculated column will check if the year and month column for the current row this calculated column formula is executed in is the same value as the year and month of the first date of this particular fiscal year:

Now lets calculate the sales amount for the first month of the current fiscal year:

=IF(HASONEVALUE(DateTable[FyYear]),

CALCULATE([Sum of Revenue]

, DateTable[FirstFyMonthofYear]=1

,  ALLEXCEPT(DateTable

,DateTable[FyYear])

)

)

This calculation will calculate the sum of revenue where the DateTable[FirstFyMonthofYear]=1 for all rows in the DateTable Except the DateTable[FyYear], meaning it will always keep the filter of DateTable[FyYear] that is created by the row context.

Now for the final calculation we simply compare the two to determine the growth:

=if([Sum of Revenue]>0

&& HASONEVALUE(DateTable[FyMonth])

,DIVIDE([Sum of Revenue]-[Revenue Base Month]

,[Revenue Base Month])

)

This formula check whether [Sum of Revenue] is greater than 0 and a single month is selected for the current cell. When this is true we divide the difference between [Sum of Revenue] and [Revenue Base Month] by [Revenue Base Month] resulting in the % change compared to the base month: