Forum Discussion
djreyes
Helper I
Helper IFiscal year
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]...
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 )Yep, that should give the right result I think
djreyesHelper I
Helper IIt 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.
johnt75
Super User
Super UserTry
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
- djreyes
Is giving me 1 not 4.
- johnt75
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- djreyes
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 )