Forum

DAX formula for any...
 
Notifications
Clear all

DAX formula for any fiscal quarter in the Calendar table

2 Posts
2 Users
0 Reactions
142 Views
(@waveman24)
Posts: 2
New Member
Topic starter
 

My fiscal year starts in September.  So in a calendar year...

Date = 9/1/2020

MONTH(dCalendar[Date]) = 9

YEAR(dCalendar[Date] = 2020

Fiscal Year = IF(MONTH(dCalendar[Date]) < 9, YEAR(dCalendar[Date]), YEAR(dCalendar[Date] - 1)

How do I set my Fiscal Quarter without a long IF statement?  Is this the shortest choice?

IF(MONTH(dCalendar[Date]) < 3, 2, IF(MONTH(dCalendar[Date]) < 6, 3, IF(MONTH(dCalendar[Date]) < 9, 4, IF(MONTH(dCalendar[Date]) < 12, 1, 2))))

 

Thanks

 
Posted : 11/04/2020 11:41 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Troy,

You can use the SWITCH fucnction:

=SWITCH(MONTH(dCalendar[Date]),9,1,10,1,11,1,12,2,1,2,2,2,3,3,4,3,5,3,6,4,7,4,8,4)

Mynda

 
Posted : 12/04/2020 6:46 am
Share: