Running Total using measure with multiple filters

[Anonymous]

Hi, I need help with a couple of measures.
For various reasons, I need to calculate parental Leave based on two measures.  I then add these measures together, and then caculate running total from it.  However, the running total is incorrect.  I've broken each measure down, and the running total on the second measure with more than filter seems to be the problem.

Conditions are as follows;
1. dynamic - based on the date slicer
2. is the count of two different condtions

  a. count where status = "PL  (_mPL3)
  b. count where status <> "PL" and EndDate > StartofMonth(max date slicer)   (_mPL4)

The individual counts for _mPL3 and _mPL4 are correct, but if I separate out the running totals for each measure, then _MPL4 is not correct.
I'm  not sure what is wrong, so if someone it would help immensely.

_mPL3 =

var count_PL = CALCULATE(COUNTROWS('hr Parental Leave2'),'hr Parental Leave2'[Employment Status Code] = "PL",USERELATIONSHIP('hr Date'[TheDate],'hr Parental Leave2'[Start Date]))

return

if (isblank(count_PL),0,count_PL)

_mPL4 =

var Date_Selected = CALCULATE(MAX('hr Date'[TheDate]),ALLSELECTED('hr Date')) 

var som_Date_Selected = DATE(year(Date_Selected), month(Date_Selected), 1) 

var count_PL_b = CALCULATE(COUNTROWS('hr Parental Leave2'),'hr Parental Leave2'[End Date] >= som_Date_Selected ,'hr Parental Leave2'[Employment Status Code] <> "PL" ,USERELATIONSHIP('hr Date'[TheDate],'hr Parental Leave2'[Start Date]))

return if (isblank(count_PL_b),0,count_PL_b)

The Running Total Measure is as follows;

_mPL4_allselected = CALCULATE(

[_mPL4],

FILTER(

ALLSELECTED('hr Date'[TheDate]),

'hr Date'[TheDate]<=MAX('hr Date'[TheDate] )

) )

MonthYear	_mPL2	_mPL2_RunningTotal	_mPL3	_mPL4	_mPL4_allselected	Should be
Mar-19	0	2	0	0	2	0
Apr-19	0	9	0	0	9	0
May-19	0	11	0	0	11	0
Jun-19	0	11	0	0	11	0
Jul-19	0	11	0	0	11	0
Aug-19	1	10	0	1	10	1
Sep-19	2	10	1	1	9	2
Oct-19	2	10	2	0	7	2
Nov-19	1	10	1	0	6	2
Dec-19	1	11	1	0	6	2
Jan-20	0	11	0	0	6	2
Feb-20	2	12	2	0	5	2
Mar-20	2	13	2	0	4	2
Apr-20	3	15	3	0	3	2
May-20	2	16	2	0	2	2
Jun-20	0	16	0	0	2	2

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could refer to the following measure:

Measure =
SUMX (
    FILTER (
        ALLSELECTED ( 'hr Date'[TheDate] ),
        'hr Date'[TheDate] <= MAX ( 'hr Date'[TheDate] )
    ),
    [_mPL4]
)

Here are some links about running total of measure.

https://community.powerbi.com/t5/Desktop/Running-total-Cumulative-sum/m-p/1104725

https://community.powerbi.com/t5/Desktop/Running-total-based-on-multiple-measures/m-p/1074564

[amitchandak]

@Anonymous , the data seem like an output table.

Can you share sample data and sample output in table format?

[Anonymous]

EmployeeKey	Preferred Name	Surname	Employment Status Code	Start Date	End Date
10100	Anna	McDougal	P	15/04/2019 0:00	26/09/2019 0:00
10101	Bea	Donaldson	P	4/03/2019 0:00	31/07/2019 0:00
10102	Lisa	Clamor	PL	18/09/2019 0:00	7/07/2020 0:00
10103	Sheila	Smith	P	25/04/2019 0:00	6/09/2019 0:00
10104	Mary	John	P	2/04/2019 0:00	20/02/2020 0:00
10105	Caroline	Neville	PL	2/12/2019 0:00	13/11/2020 0:00
10106	Libby	Crum	PL	14/10/2019 0:00	12/10/2020 0:00
10107	Julie	Spencer	PL	11/03/2020 0:00	11/12/2020 0:00
10108	Louise	Fish	PL	17/02/2020 0:00	8/05/2020 0:00
10109	Rebecca	Micac	PL	4/05/2020 0:00	5/10/2020 0:00
10110	Christina	Monger	P	27/05/2019 0:00	14/06/2019 0:00
10111	Julie	Lee	P	1/07/2019 0:00	26/08/2019 0:00
10112	Antoinette	St Trpez	P	5/08/2019 0:00	30/06/2020 0:00
10113	Cait	Toman	PL	16/03/2020 0:00	4/12/2020 0:00
10114	Belinda	Gale	P	29/04/2019 0:00	24/04/2020 0:00
10115	Monica	Bailey	PL	21/10/2019 0:00	7/08/2020 0:00
10116	Valery	Vallentine	PL	1/11/2019 0:00	30/10/2020 0:00
10117	Jordan	Pickering	PL	3/04/2020 0:00	8/07/2020 0:00
10118	Ava	Leeming	PL	5/05/2020 0:00	27/07/2020 0:00
10119	Louse	Amandeep	F	15/04/2019 0:00	31/10/2019 0:00
10120	Liv	Bozic	P	8/04/2019 0:00	2/08/2019 0:00
10121	Lee	Marks	PL	4/02/2020 0:00	16/03/2020 0:00
10122	Anthony	Tesm	PL	20/04/2020 0:00	10/07/2020 0:00
10123	Von	Young	F	1/07/2019 0:00	15/07/2019 0:00
10124	Sav	Roberts	P	11/03/2019 0:00	1/01/2020 0:00
10125	jane	McDonalds	P	23/09/2019 0:00	12/06/2020 0:00
10126	Genevieve	Tan	PL	14/04/2020 0:00	6/07/2020 0:00
10127	Gwen	Apple	P	2/05/2019 0:00	6/03/2020 0:00
10128	Krist	Canon	F	1/04/2019 0:00	3/06/2019 0:00

And if the Date Slicer Max date is set to 30/06/2020.

[amitchandak]

@Anonymous , see if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]