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
ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
1 year ago

When I use your code it partially works, future months are blanked but the cumulative running total disappears:

Running Total Closed2 = 
VAR LastVisibleDate = MAX (ResolutionTable[Date])
VAR MAXResolutionDate = MAX('Cases'[Resolution Date])
RETURN
IF(
    MAXResolutionDate <=LastVisibleDate,
    [Inactive Cases])

 

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, ArchStanton 

Thank you for providing very useful information. Based on your information, I created a data set to reproduce the problem of your initial measurement value as follows:

To do this, I corrected your measurements:

Running Total Closed2 = 
VAR LastVisibleDate = MAX ('Cases'[Resolution Date])
RETURN
    CALCULATE(
        [Inactive Cases],
        ResolutionTable[Date] <= LastVisibleDate,
        REMOVEFILTERS('Cases')
)

It now looks like this:

You can always check my attachments and ask your questions.

 

 

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 Jianpeng Li,,

    Unfortunately, its still not working ðŸ˜ž
    Your newest measure is Running Total Closed3 in my Matrix below, I don't recognise the 13366 figure at all, besides, this should be blank anyway:

    The most recent Resolution Date in the Cases table is 16/01/2025 Feb & Mar should not contain any values but they still do.

     

     

     

    Running Total Closed3 = 
VAR LastVisibleDate = MAX('Cases'[Resolution Date])
RETURN
CALCULATE(
[Inactive Cases],
   ResolutionTable[Date] <= LastVisibleDate,
   REMOVEFILTERS('Cases')
)

     

     

     

     

     

    Running Total Closed = 
VAR LastVisibleDate = MAX (ResolutionTable[Date])
VAR CurrentDate = TODAY()
RETURN
CALCULATE(
[Inactive Cases],
ResolutionTable[Date] <= LastVisibleDate && ResolutionTable[Date] <= CurrentDate,
REMOVEFILTERS('Cases'))

     

     



     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, ArchStanton 

      I understand your needs. Will you be able to check your maximum date on my model or try to return LastVisibleDate back?

      VAR LastVisibleDate = MAXX(FILTER('Cases','Cases'[statecode] ="Resolved"),'Cases'[Resolution Date])

       

      If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
      Thank you for your patience and look forward to hearing from you.

       

       

      Best Regards

      Jianpeng Li