I love the dynamic date table lesson in the power query tutorial! Can you suggest the best way to include fiscal year and fiscal quarter columns? I used fill and replace for the quarter but I'm a bit stuck on the code for fiscal year?
Hi Mary,
You can use the if statement to add a custom column for the fiscal quarters. For example, in Australia our financial year runs from July to June so I would use this formula (where you have a column for the month number called 'Month#'):
= if [#"Month#"] < 4 then "Q3" else if [#"Month#"] < 7 then "Q4" else if [#"Month#"] < 10 then "Q1" else "Q2"
You can use a similar formula for the financial year, but instead of comparing to month numbers you'd use dates.
Mynda
Hi
I'm finding it hard to work out how to use this if statement to add a financial year column to my table - do I go through month by month, and how do I tell it what year to use?
Update: I did this and it seems to have worked. I added another "Fiscal Year" column to extract just the year:
= if [Fiscal Qtr] = "Q1" then Date.AddYears([Date],1) else if [Fiscal Qtr] = "Q2" then Date.AddYears([Date],1) else [Date]
Thanks
Mardi
Well done, Mardi! Glad you figured it out yourself 🙂
