Forum Discussion
Dynamically Filtering a Cumulative Total Visual based on an End Date in a Table
I am able to calculate cumulative totals using measures but I want to view it by project rather than by a date period. When I do this, the measures show data through the current date instead of the last date for which I have data. My measure is the same as this with different names:
Cumulative total measure:
Running Labor Hrs =
var _maxDate = MAX('Date'[Date])
return
CALCULATE(sum('Labor[Labor Hrs]),FILTER(ALL('Date'),'Date'[Date] <= _maxDate))
The Date table is simply generated using a table formula:
Date = CALENDAR(DATE(2017,1,1),TODAY())
This works fine to show a running (or cumulative) total.
However, since I run the visual by the project number, I would rather have the date range end at the last active date of the project, otherwise it shows me a lot of redundant data between the last active date and the current date which could span months or years.
I have spent many hours trying to filter the Date table dynamically using references to a second table with unique value of last date and first date for each project similar to this:
[Project Dates] table
Project | Last Date | First Date |
001 | 03/10/2020 | 09/10/2019 |
002 | 05/10/2020 | 04/10/2019 |
However, I cannot seem to use this table in a way, along with page slicers that are used to select the project, to dynamically adjust the Date table’s maximum values.
I assume that because the page slicer is involved, it makes it very tricky to ensure that the Project Dates table can be used to abbreviate the Date table so as to not include dates beyond the "Last Date".
Is there a way to dynamically filter the Date table based on the “project” slicer and the related “Last Date” from the Project Dates table?
2 Replies
- parry2kSuper User
yoss42 try this, change your MAX date to get the max date of each project
Running Labor Hrs = var _maxDate = CALCULATE ( MAX('ProjectTable'[ProjectDate]), ALLEXCEPT ( 'ProjectTable', 'ProjectTable'[ProjectId] ) ) return CALCULATE(sum('Labor[Labor Hrs]),FILTER(ALL('Date'),'Date'[Date] <= _maxDate))Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡