Calculate fiscal QTD amount

sekharsahu25 — Fri, 31 Mar 2023 16:54:33 GMT

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

Re: Calculate fiscal QTD amount

Anonymous — Thu, 06 Apr 2023 09:54:36 GMT

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.

topic Calculate fiscal QTD amount in DAX Commands and Tips

Calculate fiscal QTD amount

Re: Calculate fiscal QTD amount