Daily running total with multiple dimensions: dealing with blanks

[Matt_Beck]

Hey Fabric Community, I could use a little DAX help with the best way to deal with handling filter context in running totals to properly handle blank records across 2 or 3 dimensioned daily values.

I have a rather large data set with 5 years of daily billing records for 100s of users, assigned to 1000s projects and tasks that we are using to build a historical project performance report. Here is a sample of the form of this data:

factBillable:

Project	Task	User	Date	Amount
P1	P1T1	U1	2024-01-01	$          100
P1	P1T1	U1	2024-01-05	$          200
P1	P1T1	U2	2024-01-04	$          100
P1	P1T2	U3	2024-01-10	$          300
P2	P2T1	U1	2024-02-01	$          200
P2	P2T1	U4	2024-01-15	$          100
P2	P2T2	U1	2024-01-20	$          100
P2	P2T3	U2	2024-02-20	$          200

I've been able to use DAX to easily create a running total measure that is filtered to the project that is selected in an overview table. To do this i created a dimDate table that has been related to tblBillable as follows: dimDate[Date] 1:* factBillable[Date]. I then have a DAX measure [RTProject] in a separate measures table to create the project level running total:

RTProject =
CALCULATE (
    SUM ( 'factBillable'[Amount] ),
    FILTER (
        ALLEXCEPT ( 'factBillable', 'factBillable'[Project] ),
        'factBillable'[Date] <= MAX ( 'FactBillable'[Date] )
    )
)

This measure allows me to create a running total line chart like this (filtered to P2 in the table above):

My challenge comes when trying to build a stacked area chart at the task (and if possible user) level.  I am at a loss how to do this with DAX. When I use the [RTProject] measure I only see the data points for the dates where there are entries in the data set, not continuous series (again filtered to Project P2):

What I am trying to build is a chart that looks like this:

to produce that sample, I had to modify my sample table to add rows for each Project_Task Combination starting from the day before the first entry for that combination up through theday of the last entry for any Task within the project. these added rows were given an Amount of 0 if there wasn't an entry already for that day.  for this sample I did it in excel and then added Running total formulas in the table itself before loading to Power BI. It looks like this:

I've be able to create this table via SQL in my warehouse, but it becomes massive and the visual runs out of resources when trying to apply the filter context.  is there a way I can use DAX to prepare this zero-filled helper table sized only to the filter context so that it is both performant and not incurring large storage costs?

[Matt_Beck]

Hey Ibendlin, thanks for the suggestion. Can you help me understand more about what you mean?  I tried creating a new disconnectedDate table with the following statement and did not relate it to any other tables.  

disconnectedDate = CALENDAR(Min('factBillable'[Date]),MAX('factBillable'[Date]))

when I put that date column in the x axis i just get a flatline chart like this:

Did I misinterpret your suggestion?

[lbendlin]

Did you adjust your RTProject measure to now filter by the new x axis date?

[lbendlin]

You need to use a disconnected calendar table for your X axis.