A measure to check the count of rows within the time range selected

[Anonymous]

Hi,

I was working on a table with survey data collected. The table format looks like below:

Survey ID    Earlist Date  Latest Date  Completed Date

A                 1/1/2019      1/20/2019    1/3/2019

B                 1/2/2019       1/20/2019   

Basically, all the repondents call submit their responses between the earlist date and the latest date. The completed date is the actual date that they submit their responses. If no response was received by the system within that period, it will be blank.

Now I want to measure the survey compliance based on the data I have. I will also need a time range slicer to see the survey compliance rate between a certain time range.

For the count of completed surveys, I already created a measure which is the count of non-blank rows of the Completed Date column. However, now I stuck with the measure of # of scheduled surveys.

My question will be:

1. Do I have to create a separate date table, which i will be using for my slicer for my final report?

2. How do I generate my # of scheduled surveys measure so it can be dynamic based on the time slicer I use? My measure should check if the time range I select from the time slicer is later than or equal to the earlist date and earlier than or equal to the latest date from my data.

Thanks in advance for your help!

[Wayfarer]

1. It doesn't seem to me like you would need a separate date table.  That could just muddy the waters for you a bit by having dates in the date table that don't really matter.  Example if your date table goes from 2010 - 2030, unless you have relationships set such a way or filters involved, a slicer with those dates in it would have potentially irrelevent dates.
   
   There are plenty of arguments for separate date tables, it can help a lot of time intelligence stuff.  But it's dependent on what you're doing.  A slicer can easily contain whatever dates you have in your Completed Date.
   
   
2. Sorry, what are you trying to measure?  A number of surveys that have been answered within the time limit?  What isn't working for you?  If I understand correctly:
   If you have a table showing responses, and add a slicer with your Completed Date in it, selecting any of the dates should filter the table to show only surveys completed on that date.  Or within the date range if your slicer is a range.
   
   If you want to compare the number completed within the time to the total surveys, I think your total measure would follow a CALCULATE COUNT filtered by ALLSELECTED.

[Anonymous]

Hi,

More specifically, I want to count the # of all the assigned surveys from my data. Only those rows which have a "Completed Date" are surveys which were completed by respondents; Those rows with a blank "Completed Date" mean that these surveys were sent out, but no one answered them between their "Earliest Date" and "Latest Date" from the data.

I want to be able to measure the survey compliance % based on this dataset. In this case, I cannot use "Completed Date" as the slicer since I cannot get the correct # of scheduled surveys because some of the rows have blank "Completed Date".

For example, I have my time range filtered between 11/1/2019 - 11/28/2019. There are 300 scheduled surveys that have an "Earliest Date" later than 11/1/2019 and "Latest Date" earlier than 11/28/2019, which means these surveys were supposed to be completed within the time range I selected.

When we filter the time range between 11/1/2019 - 11/28/2019. We have 200 completed calls ( count of non-blank "Completed Date" rows, and their "Completed Dates" fall between this time range). My survey compliance is 66.7% for the time range I select.

I would like to have a date slicer, so my survey compliance % can be dynamic, based on the time range I select.