cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## Cumulative total of a measure over 4 quarters

Hi,

I have the following formula to calculate the running total (cumulative) of a measure ([Left CoE]) here, over the quarter:

Cumulative on Quarter =
CALCULATE (
SUMX (
FILTER (
ALLSELECTED ( VW_CoE_OnBoard_Attrition[Quarter] ),
VW_CoE_OnBoard_Attrition[Quarter] <= MAX ( VW_CoE_OnBoard_Attrition[Quarter] )
), VW_CoE_OnBoard_Attrition[Left CoE])
)

How do I calculate the same for each quarter (Q1, Q2, Q3, Q4)? How do I add a filter for the [Quarter] field in the above formula?
7 REPLIES 7
Community Support

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.

Community Support

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User II

Hi @Anonymous

Try the formula below.

``````QTD =
CALCULATE(
[Sales],
DATESQTD( 'Calendar'[Date] )
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Anonymous
Not applicable

Hi @Mariusz,

Thanks for your reply!

DATESQTD throws an error. How do I use the formula with the [Quarter] field instead of the [Date] field?

Thanks!

Super User II

Hi @Anonymous

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?

Many Thanks

Mariusz

Anonymous
Not applicable

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 1001 ABC India XYZ Priya A 1 0 201906 2019 Q2 1002 DEF India YZX Hema B 0 0 201903 2019 Q1 1003 DDD India YZX Hema C 1 0 201903 2019 Q1 1004 YYY India XYZ Priya A 0 0 201906 2019 Q2 1002 DEF India YZX Hema B 1 0 201903 2019 Q1 1005 XXX India XYZ Priya B 0 0 201908 2019 Q3 1004 NNN India XYZ Priya C 0 0 201908 2019 Q3 1004 BBB India XYZ Priya B 1 0 201911 2019 Q4 1004 CCC India XYZ Priya A 0 0 201911 2019 Q4 1004 ZZZ India XYZ Priya A 1 0 201906 2019 Q2

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!

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors