Need help finding current quarter in a non January start.

[shophoney]

Hi,

I'm trying to create a DAX formula to figure out the current QUARTER.

Below are a few of my attempts. Our year starts Feb 1 to Jan 31.

=if(dCalendar[DATES]<today(),if(ROUNDUP(MONTH(TODAY())/3, 0)=dCalendar[QTR],"CURRENT QTD",""),"")

QTR
=if(ROUNDUP((MONTH(TODAY())-1)/3, 0)=0,4)

So, Feb-Apr would be QTR 1.

Thanks

 

[sijpie]

=SWITCH(MONTH(TODAY()),1,4,2,1,3,1,4,1,5,2,6,2,7,2,8,3,9,3,10,3,11,4,12,4)

The formula works as follows: it switches the value behind the number to be fond. So for instance for january, it looks up 1 in the list:
1,4,
2,1,
3,1,
4,1,
5,2,
6,2,
7,2,
8,3,
9,3,
10,3,
11,4,
12,4
1 has 4 attached to it.
For May (5): 5 has 2 attached to it

 

[jdellasala]

I would recommend making a Calendar Table with one or more Quarters columns. A great video for creating it is here - What is a Date Table / Calendar table in Power BI / Excel, as well as sample Excel and Power BI files with the calendar queries. All that's needed is to set the Start Date, Number of Years needed, Month Number For End Financial Year, and the First Day of the Week - all clearly explained in the sample Excel file. Once you have the calendar built, connect your table's dates with the Date column in the Calendar table, hide all dates in your tables from Client Tools so that you're only using the Calendar Dates in reports, and the Quarters for the dates in your tables will automatically be connected through the relationship of the table's dates with the Calendar Table. That should also make your DAX formulas easier to create.