Percentage of days a form has been submitted

[robinsab]

Hello,

Im pretty new to power BI and am a little stumped. I have a Power App form that submits to a sharepoint table, its a daily task list for maintenance managers. I want to create a dashboard with a measure that calulates what percentage of days someone submitted the form.

For example, if there was at least one submitted on September 1-9 then no one submitted it on the 10th or 11th but they did on the 12th and 13th, I need to calulate what percentage of those 13 days it was submitted.

There is a date column for each submission but it does not auto generate dates that are missed, so I cant just ask it to calculate what percentage of dates are filled and which are blank if that makes sense. Im looking to have a consistant percentage of 75% of days having at least one submission.

Any help would be appreciated, thank you!

[_elbpower · Accepted solution]

To calculate the percentage of days on which the form has been submitted, even if the date column doesn't contain missing dates, you can follow these steps in Power BI:

1. Create a Date table:
Create a date table that includes a complete range of dates covering the period you are interested in. You can do this using the "CALENDAR" function in Power Query or by creating a calculated table in Power BI. This table will be used to ensure that all dates are accounted for.

2. Create a relationship:
Create a relationship between your Date table and the date column in your SharePoint table, linking them by date.

3. Create a measure to calculate the percentage of days with submissions:
You can create a measure that calculates the percentage of days with at least one submission using the COUNTROWS and COUNTAX functions.

Submission Percentage = 
VAR TotalDays = COUNTROWS('Date Table')
VAR DaysWithSubmissions = COUNTAX('SharePoint Table', [Date Column])
RETURN
DIVIDE(DaysWithSubmissions, TotalDays, 0)

Replace 'Date Table' with the name of your Date table and 'SharePoint Table' with the name of your SharePoint table, and '[Date Column]' with the name of your date column.

4. Display the measure in a card visual:
Add a card visual to your Power BI report canvas and place the "Submission Percentage" measure into it. This card will display the calculated percentage of days with at least one submission.
This measure will calculate the percentage of days with at least one submission, even if there are missing dates in your SharePoint table. It ensures that the denominator (total days) includes all relevant dates from your Date table, and the numerator (days with submissions) counts the days where submissions exist in your SharePoint table.

This should give you a consistent percentage of days with submissions across the specified date range.