This next visual is to show to management the number of submissions we have received this month, and how many submissions are waiting for information. This can be useful as it will provide management an idea of the number of submissions coming in, and how many to expect by the end of the month.
Finding the number of new pending submissions received was going to be a tricky thing to find. This is because there is no standardised cut-off point for when we stop naming new submissions each month, as the dates they are named is variable. One month, we may cut-off processing submissions after the 11th, another month the 14th, this provides a problem of finding all of the submissions between the previous cut-off point and todays date.
My solution to this is to instead find the number of new submissions between the last assigned date and the current date. This does mean that several days will be missed out of the analysis, which can cause issues, but as a starting point this is what I want to try.
This is the DAX used for calculating this:
New Pending =
VAR maxdate = max('sheet1'[Assigned])
Return
COUNTROWS
(FILTER
('sheet1','sheet1'[Submitted].[Date] >= maxdate
&&
'sheet1'[Submitted] <= today()))
This graph was then created, I broke it down by the source as well, to get an idea of which submissions are coming in. I tried the horizontal bar chart, as I had watched a few video on best practices, and this was highlighted as being easier to read, due to how your eyes follow the chart..
I also added in another filter with the number of waiting submissions as well, as this shows that although we may have a large number of submissions which have not been processed, a large number require additional information.
I now need to find a better solution to the number of pending submissions. I think the best way would be to add a new flag for the last submission processed, which could be updated every month… but this would be an extra task and would require adding into the database. I am not sure if that would be a good solution. I will keep thinking of how to best improve this.
Latest update to the number of new pending submissions. The old method for finding the number of new pending submissions would find the number of submissions between the latest assigned date and the current date. This would only find a portion of the submissions, as the cut off point for naming submissions lies before the assigned date, and there could be a week or two gap between the last submission processed and the latest assigned date. So to fix this I have written a new solution in DAX to find all of these submissions.
New Pending =
VAR max_submitted = CALCULATE(max('sheet1'[Submitted]), 'Sheet1'[Status] = "PROCESSED")
return
COUNTROWS
(FILTER
('Sheet1','Sheet1'[Submitted].[Date] >= max_submitted
&&
'sheet1'[Submitted] <= TODAY()))
This creates a new variable "max_submitted" and finds the max date submitted, with a status of PROCESSED. Processed means that it has an assigned date as well. This will give the latest submission that was processed and returns it as a variable.
Next "New Pending" will count the number of rows where the submitted date is higher than the variable "max_submitted" and the submitted date is lower than todays date. This will return the number of submissions between this period.
When originally thinking about this, I was initially coming up with very complicated ideas about creating a max_assigned_date variable and then using that in conjunction with the max_submitted_date, but then realising I could just use the above solution as it really isn't as complicated as I was making it out to be, This was a below example of that idea:
New Pending =
VAR maxdate = max('sheet1'[Assigned])
VAR latest_submitted = CALCULATE(MAX('sheet1'[Submitted]),'sheet1'[Assigned] = maxdate)
Return
COUNTROWS
(FILTER
('sheet1','sheet1'[Submitted].[Date] >= latest_submitted
&&
'sheet1'[Submitted] <= today()))