Forum Discussion
Power ParticipantBlank 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(
[Inactive Cases],
ResolutionTable[Date] <= LastVisibleDate,
REMOVEFILTERS('Cases'))
Thanks
15 Replies
- bhanu_gautam
Super User
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'))
ArchStantonHi,
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'))
Wilson_Memorable Member
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
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.
- 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])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.
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!
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().
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
