Forum Discussion

johnbasha33's avatar
johnbasha33
Icon for Super User rankSuper User
2 years ago
Solved

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 he...
"DAX help" DAX
  • talespin's avatar
    talespin
    2 years ago

    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)
     

     

     

talespin's avatar
talespin
Icon for Solution Sage rankSolution Sage
2 years ago

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's avatar
    johnbasha33
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      talespin
      Icon for Solution Sage rankSolution Sage
      2 years ago

      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's avatar
        johnbasha33
        Icon for Super User rankSuper User
        2 years ago

        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?