Running total for multiple filters

[johnbasha33]

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 

[talespin]

hi @johnbasha33 

My apology, but I am unable to understand requirement.

[johnbasha33]

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

[Anonymous]

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

[johnbasha33]

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

[Anonymous]

Hi, @johnbasha33

Your original need, described by talespin in the 9th Reply has already solved your original need, please consider mark's scenario. The latter ones belong to the new requirements, and you can consider opening a new case.

Best Regards

Yongkang Hua

[talespin]

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. 

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?".

[johnbasha33]

@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 

[talespin · Accepted solution]

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)

[johnbasha33]

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

[talespin]

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

    )

[johnbasha33]

@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?

[talespin]

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

    )

[johnbasha33]

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. 

[talespin]

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.

[johnbasha33]

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.

[talespin]

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.

[johnbasha33]

@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. 

[Greg_Deckler]

@johnbasha33 Better Running Total - Microsoft Fabric Community

[johnbasha33]

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

[Greg_Deckler]

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