topic Re: Cumulative sum for QTD (Custom Quarter) in DAX Commands and Tips

Cumulative sum for QTD (Custom Quarter)

SQuazi — Mon, 24 Jul 2023 10:23:30 GMT

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.

Re: Cumulative sum for QTD (Custom Quarter)

Manoj_Nair — Mon, 24 Jul 2023 17:27:18 GMT

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) ) )

Re: Cumulative sum for QTD (Custom Quarter)

SQuazi — Tue, 25 Jul 2023 04:56:19 GMT

Manoj_Nair STARTOFQUARTER does not take 2 arguments

Re: Cumulative sum for QTD (Custom Quarter)

Kev74 — Tue, 25 Jul 2023 08:55:00 GMT

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])

Re: Cumulative sum for QTD (Custom Quarter)

v-zhangti — Thu, 27 Jul 2023 07:44:21 GMT

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.