cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User V
Super User V

Re: Cumulative total of a measure over 4 quarters

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.

 

Highlighted
Anonymous
Not applicable

Re: Cumulative total of a measure over 4 quarters

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!

Highlighted
Super User V
Super User V

Re: Cumulative total of a measure over 4 quarters

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

 

 

Highlighted
Anonymous
Not applicable

Re: Cumulative total of a measure over 4 quarters

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!

Highlighted
Community Support
Community Support

Re: Cumulative total of a measure over 4 quarters

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 it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Re: Cumulative total of a measure over 4 quarters

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.

Highlighted
Community Support
Community Support

Re: Cumulative total of a measure over 4 quarters

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 it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors