topic Re: Fiscal year in DAX Commands and Tips

Fiscal year

djreyes — Thu, 06 Apr 2023 03:41:30 GMT

Hello,

Fiscal year starts April, I tried the formula below but I am getting an error message:

Column 2 = if(QUARTER(PowerBi[Document Date].[Quarter])>=2,QUARTER(PowerBi[Document Date].[Quarter])-1,QUARTER(PowerBi[Document Date].[Quarter])+1)

The error message is: Cannot convert Qrt 1 of Type Test to type Date.

I feel like I am missing something. Please help.

Thank you!

Re: Fiscal year

johnt75 — Thu, 06 Apr 2023 08:23:57 GMT

The QUARTER function expects a date, but you're passing in the quarter from the date hierarchy. Try

Column 2 = IF ( QUARTER ( PowerBi[Document Date].[Date] ) >= 2, QUARTER ( PowerBi[Document Date].[Date] ) - 1, QUARTER ( PowerBi[Document Date].[Date] ) + 1 )

Re: Fiscal year

djreyes — Thu, 06 Apr 2023 13:42:30 GMT

It worked, question though, I am running a report from January to March which is Quarter 1 for calendar year, but in our fiscal year its quarter 4. Its giving me the result of 2 instead of 4.

Re: Fiscal year

johnt75 — Thu, 06 Apr 2023 13:51:09 GMT

Try

Column 2 = VAR CalendarQuarter = IF ( QUARTER ( PowerBi[Document Date].[Date] ) >= 2, QUARTER ( PowerBi[Document Date].[Date] ) - 1, QUARTER ( PowerBi[Document Date].[Date] ) + 1 ) VAR FiscalQuarter = MOD ( CalendarQuarter + 3, 4 ) RETURN FiscalQuarter

Re: Fiscal year

djreyes — Thu, 06 Apr 2023 13:53:09 GMT

Is giving me 1 not 4.

Re: Fiscal year

johnt75 — Thu, 06 Apr 2023 13:59:22 GMT

Probably easier to just use SWITCH

Column 2 = VAR CalendarQuarter = IF ( QUARTER ( PowerBi[Document Date].[Date] ) >= 2, QUARTER ( PowerBi[Document Date].[Date] ) - 1, QUARTER ( PowerBi[Document Date].[Date] ) + 1 ) VAR FiscalQuarter = SWITCH ( CalendarQuarter, 1, 4, 2, 1, 3, 2, 4, 3 ) RETURN FiscalQuarter

Re: Fiscal year

djreyes — Thu, 06 Apr 2023 14:03:36 GMT

Still 1, I changed the value to 3 and the result is 4, is that an option?

Column 2 = IF ( QUARTER ( PowerBi[Document Date].[Date] ) >= 2, QUARTER ( PowerBi[Document Date].[Date] ) - 1, QUARTER ( PowerBi[Document Date].[Date] ) + 3 )

Re: Fiscal year

johnt75 — Thu, 06 Apr 2023 14:05:45 GMT

Yep, that should give the right result I think