Calculate performance vs LY with shifting month dates

Question

I've tried so many times to solve this, but failed.

Question:

I help on a solution on how to calculate Current Year divided by last year in our Fiscal Year, month by month, where last years date differs from this years date

Scenario

I want to see how we performed on revenue January 2017 vs. January 2016
I select these date filters; YEAR = 2017 and Month = January. This will generate the dates 1st January to 31st January 2017
But, the dates for 2016, is not 1st January to 31st January
My company's logic is; Which DAY NAME was the 1st January of 2017 and find this first DAY NAME in January 2016 and then see which DATE this has.

Scenario Example:

2017 = 1 - 31 January and the DAY NAME for 1st January is "Sunday"
2016 = The date for the first Sunday in 2016 is 3rd January, therefore, the dates for January 2016 = 3 January - 2. February
My problem is how to make a formula that takes this year and divide that on last year's date range logic

What have I done?

First I created a formula; CALCULATE(SUM(Append1[SalesAmount]);SAMEPERIODLASTYEAR(Append1[Date]);ALL(Append1);FILTERS(Append1[Channel]))
This worked perfectly, but this just calculates 1-31 January 2017 / 1-31 January 2016 (calendar year, not fiscal year).
Googled through many, many Power BI post regarding Rolling months, DAX, DATE DAX and such, but i still haven't been able to come up with a solution.

Hope some of you can help me in the right direction!

Regards Erik

Alexis Olson Alexis Olson · Accepted Answer · 2018-01-05T16:25:43

You can create a calculated column that gives the corresponding date from the previous year as follows:

DateLastYear = 
    VAR CurrDate = Append1[Date]
    VAR LastYear = DATE(YEAR(CurrDate)-1,MONTH(CurrDate),DAY(CurrDate))
    VAR Offset = MOD(WEEKDAY(CurrDate) - WEEKDAY(LastYear),7)
    RETURN LastYear + Offset

Given this, you can create a calculated column that looks up the sales amount on those dates:

LastYearSalesAmount =
    LOOKUPVALUE(Append1[SalesAmount],Append1[Date],Append1[DateLastYear])

At this point, you should be able to create your ratio as follows:

DIVIDE(SUM(Append1[SalesAmount]), SUM(Append1[LastYearSalesAmount]))

Calculate performance vs LY with shifting month dates

1 Answers