Cumulative total of a measure over 4 quarters

[Anonymous]

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?

[v-shex-msft]

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

[Anonymous]

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.

[v-shex-msft]

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

[Mariusz]

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]

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!

[Mariusz]

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]

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!