Calculating a text column with certain parameters

[water-guy-5]

I have a bunch of restuarant locations that need to be "checked" for regulation by the 15th of each month. Unfortunately, the way our data works is that each day when someone visits the restaurant for daily logging", they are forced to fill out either "inspection taken" or "not checked". As a result, location A might have 14 entries of "not checked" and 1 entry of "inspection taken".

A simple count function won't work as it makes the data appear that we don't check the restuarants often. Here is one of my current measures that counts if the # of times we have inspected an area

CALCULATE(COUNT(Restaurant[Status]), Restuarant[Response]="Not Checked").

I would like a measure that does a date range filter from the first day of the month - fifteenth day of the month, from there, if there is any entry that has "inspection taken" then I want a switch function that returns "Monthly Check Complete". If the first fifteen days are all "Not Checked", then I would like measure to return "Investigate".

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

In the Date slicer (which should be built from the Calendar Table) select 15 July 2021.  Try this measure

Measure = if(CALCULATE(COUNT(Restaurant[Status]), Restuarant[Response]="Not Checked",DATESBETWEEN('Calendar[Date],STARTOFMONTH(Calendar[Date]),MAX(Calendar[Date])))=15,"Investigate","Monthly Check Complete")

This measure should work if the only 2 entries that can appear in the Status column are "Not checked" and "Inspection taken"