Finding previous year sales with 445 calendar

DAX DAX Calculations
1

I’m having the same issue showing this year vs previous year units sold using a custom 445 fiscal calendar. I’d like to be able to show current vs previous year sales within a hierarchy (year, quarter, month, week). This is very easy to achieve with a standard calendar, but I’m struggling to do the same with a fiscal calendar.

I’ve watched some of the videos on Enterprise DNA. I was able to successfully look at a single granularity of data (current week vs same week last year, or current quarter vs same quarter last year), however when I try to place these aggregations in a hierarchy on a table or bar chart, it doesn’t work.

I’ve attached a sample pbix file which contains a simple data model with my 445 date table (the fiscal dates are labeled FiscalYear, FiscalQuarter, etc.)., plus a transaction table (units sold = sum[units sold]. I’m hoping someone can show me how to create a dax formula that allows the user to view current vs previous data within a hierarchy based on the fiscal calendar.

So far, I’ve tried the following dax, which work only in one granularity: either year OR week, but I can’t stack these into a hierarchy to move up and down the date hierarchy. This version works with current vs previous year, but not the other levels of the date hierarchy:

Units Sold Previous FY = CALCULATE([Units Sold], filter(all(dates), dates[fiscalyear]=MAX(Dates[FiscalYear])-1) )

This next bit of DAX, which I found on enterprise DNA, works for comparing this week vs the same week last year, but you can’t drill up from week to month to quarter to year, as it returns blanks when you leave the week level of the hierarchy:

Units Sold PY Fiscal 1 =
var currentfiscalweek = SELECTEDVALUE(DatesPrim[FiscalWeeknum])
var currentfiscalyear = selectedvalue(DatesPrim[FiscalYear])

return

CALCULATE(’+Measures’[Units Sold],
FILTER(ALL(datesprim),
DatesPrim[FiscalWeeknum] = currentfiscalweek &&
DatesPrim[FiscalYear] = currentfiscalyear - 1) )

Thank you for your help!

Pete

Fiscal Cal Sample.pbix (704.8 KB)

2

Hi @pete.langlois

Is it correct that each FY has a duration of 364 days ?
(2018 starts at 4/Feb/18, 2019 at 3/Feb/2019, 2022 at 30/Jan/2022 ?)

If that is the case, “Last Year Quantity sold” could be calculated with a straight-forward DateAdd function like below.
Condition is that the Date table has defined fiscal periods per date for the grouping and drilldown.

PBIX attached: (with FY Date table and dummy order quantity for each period from 2018 till 2023)
Fiscal Calender Sample v2.pbix (644.1 KB)

Sold units Last Fiscal Year2 = CALCULATE( SUM('Order'[Units Sold]), 
                                             DATEADD(FYDateT[Date], -364, DAY ))

Less dynamic, much more flexible:

Sold units Last Fiscal Year = 
var FYDayPerFY = SWITCH(TRUE(),
      year(MAX(FYDateT[Date])) = 2018, 364,   --start FY 04/02/2018
      year(MAX(FYDateT[Date])) = 2019, 364,   --start FY 03/02/2019
      year(MAX(FYDateT[Date])) = 2020, 364,   --start FY 02/02/2020
      year(MAX(FYDateT[Date])) = 2021, 364,   --start FY 31/01/2021
      year(MAX(FYDateT[Date])) = 2022, 364,   --start FY 30/01/2022
      year(MAX(FYDateT[Date])) = 2023, 364,   --start FY 29/01/2023
      365)
return
CALCULATE([Sold units], 
             DATEADD(FYDateT[Date],-FYDayPerFY,day))

With the mentioned formulas, drill down from year to weeks and days is possible without any problem.

A possible report , based upon the dummy test data :

image
image643×538 22.4 KB

Please let me know your thoughts,

Kind regards, Jan van der Wind

1 Like
3

Thank you so much! Such a simple and elegant answer. I hadn’t figured out that with the 445 calendar, every year has exactly 364 days, so the simple dateadd worked wonders. I’ve been trying to figure this out for over week - you’re a lifesaver!