Forum Discussion

yoss42's avatar
yoss42
Frequent Visitor
5 years ago

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:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-Total-with-Max-Date-Filter/m-p/769187/highlight/true#M3589

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

  • 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.

    • yoss42's avatar
      yoss42
      Frequent Visitor

      parry2k 

       

      Thanks for the suggestion but this seems to break the measure, all it returns is the same value for every date rather than a cumulative running total.

       

      parry2k version:

       

       

       

      My semi-working version but with no maximum date cutoff: