cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate fiscal QTD amount

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

1 ACCEPTED SOLUTION
Community Support

I suggest you to create a calendar table by my code.

``````Calendar =
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.

Community Support

I suggest you to create a calendar table by my code.

``````Calendar =
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.