Trouble with Fiscal Year Y/Y Calculations

[Anonymous]

Hi,

A quick question for those of you who are familiar with date tables and time intelligence in PowerBI:

I have a fiscal date table setup in my PowerBI. I'm trying to do Y/Y calculations with the SAMEPERIODLASTYEAR function. The function itself works, but, given that our fiscal year doesn't start on the exact same day every year, I'm running into an issue.

I.e., FY21-Q1 began on February 1st, 2020 whereas FY22-Q1 began on January 30th, 2021. For my Y/Y calculations, PowerBI obviously doesn't consider this... So, PowerBI compares January 30th, 2021 to January 30th, 2020 rather than February 1st, 2020. Instead of comparing exact dates to exact dates, I want to compare Day 1 of Week 1 of Quarter 1 of FY21 to Day 1 of Week 1 of Quarter 1 of FY20... Does this make sense?

Any ideas?

[v-jayw-msft]

Hi @Anonymous ,

If you already have the Fiscal Year date, you could use WEEKDAY(), WEEKNUM() and QUARTER() function to get the corresponding values of each day. Then use these values as the filter conditions in your formulas.

https://docs.microsoft.com/en-us/dax/weekday-function-dax 

https://docs.microsoft.com/en-us/dax/weeknum-function-dax 

https://docs.microsoft.com/en-us/dax/quarter-function-dax 

Best Regards,

Jay

[mahoneypat]

You'll need to make a custom date table and write measures that use a variable get the current FY and then subtract 1.  Please see if this date table gets you close.

445 Calendar with 53-Week Years – Hoosier BI

Pat