Blank out future running total values

[ArchStanton]

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(
        [Inactive Cases],
        ResolutionTable[Date] <= LastVisibleDate,
        REMOVEFILTERS('Cases'))
    

Thanks

[Anonymous]

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]

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]

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]

Ps I changed the EndDate from 31 Mar 2025 to MAX('Cases'[Resolution Date] and it sort of works but I've now lost the future months if I need to put them on the x axis of a chart. This is not what I'm looking for.

[ArchStanton]

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?

[Anonymous]

Thank you Wilson_ and bhanu_gautam 

Hi, @ArchStanton 

I'm guessing that the fiscal year month column on your left is from your date table, and your date table has a 1-to-many relationship with the ResolutionTable table.

Build the following measure on top of keeping your measure:

Measure 3 = IF(MIN('Table'[Date])<=MAX('ResolutionTable'[Date]),[Running Total Closed],BLANK())

This will determine the minimum date of each month in your matrix and then compare it to your ResolutionTable[Date] maximum date, ensuring that your measure is only executed within the maximum date, and future dates beyond that are returned empty.

I've uploaded the PBIX file I used this time below.

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]

Thank you. I've replied to Wilson because his DAX didn't work as there was some confusion as to what was my Calendar table and what was my FACT table, hopefully this extra detail will make sense to you both!

[Wilson_]

Hi ArchStanton,

I'm assuming your matrix row headers come from a separate date table. If so, try the below:

Running Total Closed = 
VAR LastVisibleDate = MAX ( ResolutionTable[Date] )
VAR MaxCalendarDate = MAX ( Calendar[Date] )
RETURN
    IF ( 
        MaxCalendarDate <= LastVisibleDate,
        [Inactive Cases]
    )

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[ArchStanton]

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]

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]

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]

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

[ArchStanton]

Hi,

Sorry, that didn't work, I think I need to provide the following detail:

My 2nd calendar table is called Resolution Table and it has a One to Many relationship with my Cases table.

So, the calendar ResolutionTable[Date] field has an active relationship with the 'Cases'[Resolution Date] (this is not a continguous list of dates by the way - not every case has a Resolution Date!).

I have another Calendar table that deals with Active cases only - it has been marked as a DateTable whereas the ResolutionTable has not.

My InactiveCases measure simply counts those cases that have a 'Resolved' status.

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

If a case has a Resolution Date then it will have a 'Resolved' status.

[bhanu_gautam]

@ArchStanton ,Try using 

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

[ArchStanton]

Hi,

I've copied your code (see below) and there's still no change to the results.

This (Resolution Table) is not my main date table, do you think that has that got anything to do with this not working?

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