Calculate fiscal QTD amount

[sekharsahu25]

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

[Anonymous · Accepted 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.