Forum Discussion
Running total for multiple filters
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
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)
20 Replies
Hi talespin it is little hard and complex as i mentioned before, how about we can have a quick call ...
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
Greg_Deckler its not a straight forward situation, pls read my question one more time.
johnbasha33 Without sample data and expected output it's near impossible to be specific.
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?".
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 amitchandakhi 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)
