cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
v-shex-msft
Community Support
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.

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.
Mariusz
Super User II
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!

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:

 

WWIDCW NameCountrySupplierSponsor NameCoE DisciplineActiveOnBoardedYearMMYearQuarter
1001ABCIndiaXYZPriyaA102019062019Q2
1002DEFIndiaYZXHemaB002019032019Q1
1003DDDIndiaYZXHemaC102019032019Q1
1004YYYIndiaXYZPriyaA002019062019Q2
1002DEFIndiaYZXHemaB102019032019Q1
1005XXXIndiaXYZPriyaB002019082019Q3
1004NNNIndiaXYZPriyaC002019082019Q3
1004BBBIndiaXYZPriyaB102019112019Q4
1004CCCIndiaXYZPriyaA002019112019Q4
1004ZZZIndiaXYZPriyaA102019062019Q2


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:

 

leftcoe.png

 

 

Please let me know if my query makes sense!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors