Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Need help in getting Fiscal QTD calculations, My fiscal calendar starts from June , I need to calculate QTD dynamically. As you can see below for the Calendar QTD it should show $159,411,930.304 but for the fiscal QTD it should show $104,953,597.637
Solved! Go to Solution.
Hi @sekharsahu25 ,
I suggest you to create a calendar table by my code.
Calendar =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMMM" ),
"MonthSort", MONTH ( [Date] ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] ),
"Calendar QTD",
"Qtr" & " "
& QUARTER ( [Date] ),
"Fiscal QTD",
VAR _START =
EOMONTH ( [Date], -6 ) + 1
RETURN
"Q" & " "
& QUARTER ( _START ),
"Year Calendar QTD",
YEAR ( [Date] ) * 100
+ QUARTER ( [Date] ),
"Year Fiscal QTD",
VAR _START =
EOMONTH ( [Date], -6 ) + 1
RETURN
IF (
MONTH ( [Date] ) >= 6,
YEAR ( [Date] ) * 100
+ QUARTER ( _START ),
( YEAR ( [Date] ) - 1 ) * 100
+ QUARTER ( _START )
)
)
Measure:
Amount =
VAR _CURRENT =
CALCULATE (
MAX ( 'Calendar'[Year Fiscal QTD] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = TODAY () )
)
VAR _QTDAmount =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year Fiscal QTD] = _CURRENT )
)
VAR _Amount =
CALCULATE ( SUM ( 'Table'[Amount] ) )
RETURN
IF (
HASONEVALUE ( 'Calendar'[Fiscal QTD] ),
IF ( NOT ( ISBLANK ( _Amount ) ), _Amount, BLANK () ),
_QTDAmount
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sekharsahu25 ,
I suggest you to create a calendar table by my code.
Calendar =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMMM" ),
"MonthSort", MONTH ( [Date] ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] ),
"Calendar QTD",
"Qtr" & " "
& QUARTER ( [Date] ),
"Fiscal QTD",
VAR _START =
EOMONTH ( [Date], -6 ) + 1
RETURN
"Q" & " "
& QUARTER ( _START ),
"Year Calendar QTD",
YEAR ( [Date] ) * 100
+ QUARTER ( [Date] ),
"Year Fiscal QTD",
VAR _START =
EOMONTH ( [Date], -6 ) + 1
RETURN
IF (
MONTH ( [Date] ) >= 6,
YEAR ( [Date] ) * 100
+ QUARTER ( _START ),
( YEAR ( [Date] ) - 1 ) * 100
+ QUARTER ( _START )
)
)
Measure:
Amount =
VAR _CURRENT =
CALCULATE (
MAX ( 'Calendar'[Year Fiscal QTD] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = TODAY () )
)
VAR _QTDAmount =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year Fiscal QTD] = _CURRENT )
)
VAR _Amount =
CALCULATE ( SUM ( 'Table'[Amount] ) )
RETURN
IF (
HASONEVALUE ( 'Calendar'[Fiscal QTD] ),
IF ( NOT ( ISBLANK ( _Amount ) ), _Amount, BLANK () ),
_QTDAmount
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |