Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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- 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)
)
)
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])
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |