Calculate cumulative counts over missing date/time values

[Anonymous]

I'm running a survey and trying to calculate and project completion rates. Here's where I'm at so far:

I can get a cumulative completion rate using a formula like this (I have one for both counts and percentage):

• Cumulative Comparison Count = CALCULATE(
  [Comparison Completers],
  FILTER(ALL(ComparisonData[Relative End Date (bins)]),
  ComparisonData[Relative End Date (bins)]<=MAX(ComparisonData[Relative End Date (bins)])))

The problem is that where there are missing "Relative End Date" values (i.e. when no one submitted a survey during that time period), the chart skips that bucket and this breaks the projection function.

In excel I would create a table of all the date/time values between my start date and end date and then use a countif function like this:

A1     1/1/2019 6:00:00 AM     =COUNTIF(CompletionDate/Time,"<="&A1)

A2     1/1/2019 7:00:00 AM     =COUNTIF(CompletionDate/Time,"<="&A2)

A3     1/1/2019 8:00:00 AM     =COUNTIF(CompletionDate/Time,"<="&A3)

I was able to replicate this at the whole survey level using a calculated table and a Generateseries() statement, but I need to be able to slice this data by various cuts. The calculated table doesn't cut on filters, so I'm not sure where to go from here. Is there a way to force the line chart I'm using, or the formulas, to count at every bucket value and not just at the ones where there are data?

Or another way - is there a way to create a series of values (each hour between my survey start and end time) and create a measure that counts the cumulative total at each point, regardless of whether or not there are new data at that time point?

[Anonymous · Accepted solution]

I figured it out. I used guidance from this post: https://community.powerbi.com/t5/Desktop/Cumulative-count-not-working/m-p/651707#M312569 I used a submission date/time that I rounded to the nearest hour. Then I use a calculated table like this: Time Series = GENERATESERIES(43587,MROUND(NOW(),(1/24)),(1/24)) the MROUND(NOW()) statement allows me to have a dynamic max value in that series. This means my projections can work based on the latest submission data. Projections are now working! Woohoo!