topic Re: Dynamic Fiscal Year in DAX Commands and Tips

Dynamic Fiscal Year

sam_hoccane — Thu, 22 Dec 2022 15:24:26 GMT

Hello Community,

Woould you please help me to determine how to create column that can Identify Current fiscal Quater. I have tried with this formula but it is not working. ( I am donot have dates table)

IF ( Today () >= DATE(2022,11,01) && Today() <=DATE (2023,01,31) , "Q1" ,"")

IF ( Today () >= DATE(2023,02,01) && Today() <=DATE (2023,04,30) , "Q2" ,

IF ( Today () >= DATE(2023,05,01) && Today() <=DATE (2023,07,31) , "Q3" ,

IF ( Today () >= DATE(2023,08,01) && Today() <=DATE (2023,10,31) , "Q4" ,

"NA" ))))

Our Fiscal Quarter starts from:
Q1 : Nov to Jan
Q2: Feb to April
Q3: May to July

Q4: Aug to oct

Expected Results :

Due Date (M/D/Y)	Current Quarter
01/19/2023	Q1
10/31/2022	Q1
11/22/2022	Q1
12/15/2022	Q1
02/17/2023
07/18/2023
09/18/2023
10/25/2023
11/14/2023

Thankyou in Anticipation.

Re: Dynamic Fiscal Year

djurecic — Thu, 22 Dec 2022 15:42:46 GMT

Hi sam_hoccane ,

I would recommend using a date table like the one below which you can adjust to fit your fiscal calendar:

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

Re: Dynamic Fiscal Year

AlexisOlson — Thu, 22 Dec 2022 19:36:44 GMT

They've also recently released custom templates:
https://www.sqlbi.com/articles/customizing-date-and-time-intelligence-templates-in-bravo-for-power-bi/

sam_hoccane, I'd strongly recommend using a date table.
https://radacad.com/do-you-need-a-date-dimension

One way to define your fiscal quarter:

ROUNDDOWN ( MOD ( MONTH ( [Date] ) + 1 , 12 ) / 3, 0 ) + 1

Re: Dynamic Fiscal Year

ppm1 — Fri, 23 Dec 2022 00:11:53 GMT

Definitely agree on adding a Date table. Either way, you can easily create your FQ column with an expression like this:

FQ = "Q" & QUARTER(EOMONTH(Table[DueDate], 2))

Replace Table[DueDate] with your actual Table[Column]

Pat

Re: Dynamic Fiscal Year

AlexisOlson — Fri, 23 Dec 2022 01:40:46 GMT

Nice! Much cleaner than what I proposed.

Re: Dynamic Fiscal Year

sam_hoccane — Fri, 23 Dec 2022 13:07:39 GMT

Thankyou all!