Cumulative sum for QTD (Custom Quarter)

[SQuazi]

We have fortnightly data and ask is to show cumulative sum of ProductCount on MTD, YTD and QTD level.

In this requirement, FY starts from Sep and Q1=Sep-Nov and so on

MTD and YTD is working as expected, but since for QTD we do not have any option to define quarter start.

TestQTD is the expected output in screenshot

Kindly suggest possible solution.

Appreciate your inputs.

[v-zhangti · Accepted solution]

Hi, @SQuazi 

You can try the following methods. The fiscal year calendar is shown.

Measure:

TestQTD = 
CALCULATE ( SUM ( 'Table'[ProductCount] ),
    FILTER ( ALL ( 'Date' ),
        [Date] <= SELECTEDVALUE ( 'Table'[ReportingDate] )
            && [FYQ] = SELECTEDVALUE ( 'Date'[FYQ] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SQuazi]

@Manoj_Nair STARTOFQUARTER does not take 2 arguments 

[Manoj_Nair]

@SQuazi- Check this out, let me know if this work for you.

ProductValue QTD =
CALCULATE(
    SUM('Table'[ProductValue]),
    FILTER(
        ALL('Table'),
        'Table'[Date] <= MAX('Table'[Date])
            && YEAR('Table'[Date]) = YEAR(MAX('Table'[Date]))
            && STARTOFQUARTER('Table'[Date], 3) = STARTOFQUARTER(MAX('Table'[Date]), 3)
    )
)

[Kev74]

Hi try it :

QTD = 
Var QuarterNumber = INT( (MOD('Table'[Date].[NbMonth] + 3, 12)) / 3)
RETURN
SUMX(FILTER('Table', INT( (MOD('Table'[Date].[NbMonth] + 3, 12)) / 3) == QuarterNumber && 'Table'[Date] < EARLIER('Table'[Date]) + 1 && 'Table'[Date].[Year] == EARLIER('Table'[Date].[Year])), 'Table'[ProductCount])