Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.