I have the following formula to calculate the running total (cumulative) of a measure ([Left CoE]) here, over the quarter:
Try the formula below.
QTD = CALCULATE( [Sales], DATESQTD( 'Calendar'[Date] ) )
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!
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 ) ) )
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] )
Find out who's part of the program this season, and welcome the new Super Users.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Learn about the exciting things that happened in July.
All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.
Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.