I have the following formula to calculate the running total (cumulative) of a measure ([Left CoE]) here, over the quarter:
HI @Anonymous ,
Maybe you can try to use RIGHT function and year field to calculate rolling on the quarter field:
Cumulative on Quarter = VAR currYear = MAX ( VW_CoE_OnBoard_Attrition[Year] ) VAR currQuarter = MAX ( VW_CoE_OnBoard_Attrition[Quarter] ) RETURN CALCULATE ( SUM ( VW_CoE_OnBoard_Attrition[Left CoE] ), FILTER ( ALLSELECTED ( VW_CoE_OnBoard_Attrition ), VW_CoE_OnBoard_Attrition[Year] = currYear && RIGHT ( VW_CoE_OnBoard_Attrition[Quarter], 1 ) <= RIGHT ( currQuarter ) ) )
Thank you for the reply @v-shex-msft!
The SUM() function throws an error. Please note that [Left CoE] is a measure, and not an existing column.
HI @Anonymous ,
Maybe you can try to use sumx to invoke that measure:
Cumulative on Quarter = VAR currYear = MAX ( VW_CoE_OnBoard_Attrition[Year] ) VAR currQuarter = MAX ( VW_CoE_OnBoard_Attrition[Quarter] ) RETURN SUMX ( FILTER ( ALLSELECTED ( VW_CoE_OnBoard_Attrition ), VW_CoE_OnBoard_Attrition[Year] = currYear && RIGHT ( VW_CoE_OnBoard_Attrition[Quarter], 1 ) <= RIGHT ( currQuarter ) ), [Left CoE] )
Try the formula below.
QTD = CALCULATE( [Sales], DATESQTD( 'Calendar'[Date] ) )
Thanks for your reply!
DATESQTD throws an error. How do I use the formula with the [Quarter] field instead of the [Date] field?
The best practice is to design a model with Date Dimension / Calendar Table so you can use built-in time intelligence functions.
However, if you think in your case it's impossible to do so, then please can you provide a small data sample?
Thank you @Mariusz!
I use the DirectQuery mode, therefore time intelligence functions wouldn't work. 😞 Also, I do not have a date field in this particular table, I would have it use it from other tables.
Here's a sample of the data:
|WWID||CW Name||Country||Supplier||Sponsor Name||CoE Discipline||Active||OnBoarded||YearMM||Year||Quarter|
I have a measure [Left CoE] that filters on [Active] = 0.
I am trying to get the running totals of [Left CoE] over each quarter, Q1, Q2, Q3, Q4, separately, in order to create a graph like this:
Please let me know if my query makes sense!
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates