Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.