Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
1 year ago

Blank out future running total values

Hi, How can I amend my code to remove the future running total values below? Running Total Closed = VAR LastVisibleDate = MAX (ResolutionTable[Date]) RETURN CALCULATE( [Inacti...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, ArchStanton 

I envision your case, there are other states in your case table:

Using MAX directly('Cases'[Resolution Date]) will fetch the date of the non-sovled state, which will cause future dates to have values:

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    1 year ago

    Hi,

    A case that has a Resolution date can only have one statecode which is "Resolved", if the case does not have a Resolution date then the case is Active (my Inactive Cases measure is below):

     

    Inactive Cases = CALCULATE(
                DISTINCTCOUNT('Cases'[incidentid]),
                'Cases'[statecode] = "Resolved")

     

     So there shouldn't really be any future values as the latest / last Resolution Date is the 16th Jan, therefore Feb & Mar should be Blank().

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, ArchStanton 

      Thank you very much for your reply. Can you describe how my sample data differs from yours? So that I can better identify the problem and provide you with a better solution.
      Or you provide some virtual data/screenshots that don't contain privacy.

       

      Best Regards

      Jianpeng Li

      • ArchStanton's avatar
        ArchStanton
        Icon for Power Participant rankPower Participant
        1 year ago

        No problem, hope the following makes sense.

        Your Cases Table should not contain future resolution dates, cases are 'active' and do not have a resolution date until they are closed - you cannot close something in the future!
        My Cases table has the 16th Jan as the latest resolution date, I have not refreshed it for a few days but I don't need to do this.

        My resolution Table is essentially a copy of my main Date Calendar table:


        Maybe i should change the EndDate to be the YEAR(MAX('Cases'[Resolution Date])) instead of 31st Mar?