The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
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
)
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.
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
Is giving me 1 not 4.
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
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
)
Yep, that should give the right result I think
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
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
31 | |
30 | |
24 | |
22 |