topic Re: Running total for multiple filters in DAX Commands and Tips

Running total for multiple filters

johnbasha33 — Mon, 18 Mar 2024 17:30:14 GMT

Hi,

I am running out of ideas here, can you please help me with the below.

if you notice below i want to show the running total for no cases opened based on inception date for each year.

but here is the catch. year(inceptiondate)<> year(resolution date) and also we shoud check if there is any case opened previous year and got closed in the selected year.

for example in line chart, 2014 has 3 resolution dates in the left table it means, all those 3 are not valid. we should see only 1 for 2014 in line chart, that one record belongs to 2012. same count i need to get for all years as a running total. how to achieve this?

MatterID	InceptionDate	ResolutionDate
20111128-685	11/28/2011	1/20/2014
20120905-684	9/5/2012	5/8/2023
20131001-683	10/1/2013	7/1/2014
20140101-682	1/1/2014	1/1/2014
20150401-681	4/1/2015	5/3/2022

talespin lbendlin 123abc Jihwan_Kim Greg_Deckler amitchandak

Re: Running total for multiple filters

Greg_Deckler — Mon, 18 Mar 2024 16:54:26 GMT

johnbasha33 Better Running Total - Microsoft Fabric Community

Re: Running total for multiple filters

johnbasha33 — Mon, 18 Mar 2024 17:09:02 GMT

Greg_Deckler its not a straight forward situation, pls read my question one more time.

Re: Running total for multiple filters

Greg_Deckler — Mon, 18 Mar 2024 17:23:14 GMT

johnbasha33 Without sample data and expected output it's near impossible to be specific.

Re: Running total for multiple filters

talespin — Thu, 21 Mar 2024 10:46:32 GMT

hi johnbasha33

Sorry, I am unable to understand the requirement.

Understood

if you notice below i want to show the running total for no cases opened based on inception date for each year.

Please explain below in detail. What do you mean by case, closed, selected year?

Please share pbix file with mock data.

"but here is the catch. year(inceptiondate)<> year(resolution date) and also we shoud check if there is any case opened previous year and got closed in the selected year.

Re: Running total for multiple filters

johnbasha33 — Thu, 21 Mar 2024 16:40:37 GMT

talespin thanks for showing interest in this, attaching the pbix with more detailed explanation.

https://drive.google.com/file/d/1-6crHQAZAbiD3YEKynvjdny_wlL-HhJT/view?usp=sharing

lbendlin Jihwan_Kim amitchandak

Re: Running total for multiple filters

talespin — Fri, 22 Mar 2024 05:40:19 GMT

hi johnbasha33

My Apology, but I am not sure if I have understood your requirement correctly.

You want running total(Count) until you see a case closure(Created year = closed year), if that happens you want to reset running total(Count)?

Below Calculated Column will return count based on what I have stated above.

Active Count =

VAR _CreatedDate = 'Actual Data'[Created Date]

VAR _MaxClosedDate = CALCULATE( MAX('Actual Data'[Created Date]), REMOVEFILTERS(), YEAR('Actual Data'[Created Date]) = YEAR('Actual Data'[Closed Date]) && 'Actual Data'[Created Date] <= _CreatedDate)

VAR _MinClosedDate = CALCULATE( MIN('Actual Data'[Created Date]), REMOVEFILTERS())

VAR _ClosedDate = IF( ISBLANK(_MaxClosedDate), _MinClosedDate, _MaxClosedDate)

RETURN CALCULATE( COUNT('Actual Data'[CaseID]), REMOVEFILTERS(), 'Actual Data'[Created Date] >= _MaxClosedDate && 'Actual Data'[Created Date] <= _CreatedDate)

-----------------------------------------------------------------------------------------------------

If you want it as a measure.

Actice Count New =

VAR _CreatedDate = SELECTEDVALUE('Actual Data'[Created Date])

VAR _MaxClosedDate = CALCULATE( MAX('Actual Data'[Created Date]), REMOVEFILTERS(), YEAR('Actual Data'[Created Date]) = YEAR('Actual Data'[Closed Date]) && 'Actual Data'[Created Date] <= _CreatedDate)

VAR _MinClosedDate = CALCULATE( MIN('Actual Data'[Created Date]), REMOVEFILTERS())

VAR _ClosedDate = IF( ISBLANK(_MaxClosedDate), _MinClosedDate, _MaxClosedDate)

RETURN CALCULATE( COUNT('Actual Data'[CaseID]), REMOVEFILTERS(), 'Actual Data'[Created Date] >= _MaxClosedDate && 'Actual Data'[Created Date] <= _CreatedDate)

Re: Running total for multiple filters

johnbasha33 — Fri, 22 Mar 2024 07:39:31 GMT

Hi talespin thank you so much for your valuable time in this for me. it was briliant. calculation seems to be right for begining. but when i added the whole data, calculation seems to be off starting from year 2017 suddenly the count dropped to 1. that seems to be wrong.

now we also have open cases.
can you please help me fix the logic for closed ones and finally we need to take the count of cases for open cases and club both count for each year.

really appreciate if you can share it, attaching the new file here.

https://drive.google.com/file/d/1-6crHQAZAbiD3YEKynvjdny_wlL-HhJT/view?usp=sharing

Re: Running total for multiple filters

talespin — Sat, 23 Mar 2024 10:04:01 GMT

hi johnbasha33

Please apply status filter.

Actice Count New =

VAR _CreatedDate =

SELECTEDVALUE ( 'Actual Data'[Created Date] )

VAR _MaxClosedDate =

CALCULATE (

MAX ( 'Actual Data'[Created Date] ),

REMOVEFILTERS(),

'Actual Data'[Status] = "Closed",

YEAR ( 'Actual Data'[Created Date] ) = YEAR ( 'Actual Data'[Closed Date] )

&& 'Actual Data'[Created Date] <= _CreatedDate

)

VAR _MinClosedDate =

CALCULATE ( MIN ( 'Actual Data'[Created Date] ), REMOVEFILTERS(), 'Actual Data'[Status] = "Closed" )

VAR _ClosedDate =

IF ( ISBLANK ( _MaxClosedDate ), _MinClosedDate, _MaxClosedDate )

RETURN

CALCULATE (

COUNT ( 'Actual Data'[CaseID] ),

REMOVEFILTERS(),

'Actual Data'[Status] = "Closed",

'Actual Data'[Created Date] >= _ClosedDate

&& 'Actual Data'[Created Date] <= _CreatedDate

)

Re: Running total for multiple filters

johnbasha33 — Tue, 26 Mar 2024 16:28:41 GMT

talespin requirement has changed now, earlier we are not counting year(created)=year(closed).
but now we need to count them as well.
here is the expected output. how can we alter the logic now?

Re: Running total for multiple filters

talespin — Wed, 27 Mar 2024 05:13:41 GMT

hi johnbasha33

Please try this measure.

Active Count2 =

VAR _CreatedDate =

SELECTEDVALUE ( 'Actual Data'[Created Date] )

VAR _MaxClosedDate =

CALCULATE (

MAX ( 'Actual Data'[Created Date] ),

REMOVEFILTERS(),

'Actual Data'[Status] = "Closed",

YEAR ( 'Actual Data'[Created Date] ) = YEAR ( 'Actual Data'[Closed Date] )

&& 'Actual Data'[Created Date] < _CreatedDate

)

VAR _MinClosedDate =

CALCULATE ( MIN ( 'Actual Data'[Created Date] ), REMOVEFILTERS(), 'Actual Data'[Status] = "Closed" )

VAR _ClosedDate =

IF (

ISBLANK ( _MaxClosedDate ),

_MinClosedDate - 1,

_MaxClosedDate

)

RETURN

CALCULATE (

COUNT ( 'Actual Data'[CaseID] ),

REMOVEFILTERS(),

'Actual Data'[Status] = "Closed",

'Actual Data'[Created Date] > _ClosedDate

&& 'Actual Data'[Created Date] <= _CreatedDate

)

Re: Running total for multiple filters

johnbasha33 — Wed, 27 Mar 2024 06:03:28 GMT

Hi talespin thanks for your valuable time. i am expecting count 2 for 2015 as we have only 2 cases active until 2015. we spend alot of time on this, i agree this is a trickier and complex one.really appreciate your vaulable time. you have very good understanding in DAX.

Re: Running total for multiple filters

talespin — Wed, 27 Mar 2024 06:50:29 GMT

hi johnbasha33

Please highlight/share screenshot where the issue is and explaining in detail what the issue is, assuming underlying data is same as in the sample pbix file which you shared earlier.

Re: Running total for multiple filters

johnbasha33 — Wed, 27 Mar 2024 12:15:03 GMT

Hi talespin thanks for helping, this seems to be complicated,

here is the latest pbix file with sample data.
https://drive.google.com/file/d/1-6crHQAZAbiD3YEKynvjdny_wlL-HhJT/view?usp=drive_link

this is what i am expecting to see the Dynamic cummulative count.

and finally this is what i am expecting to see in the grapth for each created year

i know it is a challenging one, lets hope we can crack this one last time.

Re: Running total for multiple filters

talespin — Thu, 28 Mar 2024 04:44:41 GMT

hi johnbasha33

I am not able to understand the requirement.

For row5 in your screenshot the count starts from 2.

For row8 YEAR(Created Date) = YEAR(Closed Date) but the count doesn't reset.

Re: Running total for multiple filters

johnbasha33 — Thu, 28 Mar 2024 06:30:24 GMT

talespin Hi, it doesnt have to reset. we are no longer considering YEAR(Created Date) = YEAR(Closed Date) this condition. even though there is YEAR(Created Date) = YEAR(Closed Date) we consider it in the active count.
YEAR(Closed Date) should be less than YEAR(Created Date) for the count.

Re: Running total for multiple filters

talespin — Thu, 28 Mar 2024 06:46:19 GMT

hi johnbasha33

My apology, but I am unable to understand requirement.

Re: Running total for multiple filters

johnbasha33 — Thu, 28 Mar 2024 08:37:40 GMT

Hi talespin it is little hard and complex as i mentioned before, how about we can have a quick call ...

Re: Running total for multiple filters

Anonymous — Tue, 02 Apr 2024 07:39:32 GMT

Hi, johnbasha33

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

Re: Running total for multiple filters

johnbasha33 — Tue, 02 Apr 2024 08:31:49 GMT

Anonymous still dint have any solution for this, it is a complex scenario. so looking for any solutions.