Cumulative sum for non-continuous dates: filling in daily sums for unique records

[earthling]

Hi all I am having difficulty solving (what seems to be) a complex cumulative sum problem.

Each row below is a unique individual with their own term (Start date, End date), and daily wage earned for their respective term (Salary Amount D is not total $ earned for the term, but the $ earned per day of the term).

I need a continuous daily cumulative sum of Salary Amount D for each record, up to its end date, across all records for the period. However:

• Start and End dates are not uniform across all records
• Start dates are not continuous thus there is no continuous 'daily wage' for each record
• Creating an overall cumulative sum is straight forward, but the sum for each row must stop at it's respective end date

The situation is modeled (somewhat) graphically below, with each colored line representing a unique row start and end dates, all occuring within the minimum Start, and max End

For context, My goal is to see on which specific date on the timeline a cumulative earnings target was met.

[DataInsights · Accepted solution]

@earthling,

Try this solution.

1. Create calculated table:

DailySalary = 
GENERATE ( Salary, CALENDAR ( Salary[Start Date], Salary[End Date] ) )

2. Create date table (if you don't already have one):

DimDate = CALENDARAUTO()

3. Create relationship:

4. Create measures:

Salary = SUM ( DailySalary[Salary Amount D] )

Cumulative Salary = CALCULATE ( [Salary], DimDate[Date] <= MAX ( DimDate[Date] ) )

5. Create visual using DimDate[Date]:

Sample data:

[earthling]

@DataInsights 

This is great so far. However I am running into an issue of Cumulative Sum summing Emp ID wages outside of their term dates:

For example EMP ID 1, 2, 3 did not start on 10/02/2021. What could be causing this? Here are my meaures for reference

DailySalary = GENERATE( Data, CALENDAR(MIN(Data[Start Date]), MAX(Data[End Date]) ))

DateDim = CALENDARAUTO()

Salary = SUM( DailySalary[Salary Amount D])

Cumulative Salary = CALCULATE( [Salary], 'DateDim'[Date] <= MAX('DateDim'[Date]))

Data in visual:

[DataInsights]

@earthling,

Try removing MIN and MAX from the calculated table DailySalary. The GENERATE function is an iterator, so there is a row context.

[earthling]

Perfect. I had recieved an error when removing min and max, as 2 End Date rows were null. The problem was resolved after filtering out the nulls in power query.