Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |