Count of Distinct Clients for 3 Consecutive Days based on a Selected Value from a Slicer

[MITHUN_NAIR]

Hi All,

Need your help in Solving the Below Issue.

if I Select a Date from the Slicer(Report Levvel) i.e. 28/08/2021 I should get the Count of Distinct Clients based on the below Condition.

KPI=Not met Or Quality=Not Met .

It should compare the above Condition for three consecutive days i.e 26th ,27th & 28th
if any of this column (KPI & Quality) is Not Met for all 3 rows for each client then find the Distinct Count of Clients from the Above Data. The Result would be 1(Distinct Count of Clients-A).
Only take the count if all the 3 rows satisfies the condition

I have Created a Measure  for the Specific Date that is 28th July 

DAX Code: Measure

Counts =
VAR Selected_Date =
SELECTEDVALUE ( Date[Date] )
VAR CountofClients =
CALCULATE (
DISTINCTCOUNT ( Clients[Client] ),
FILTER ( Clients, Clients[KPI] = "Not Met" || Clients[Quality] = "Not Met" ),
Clients[Date] = Selected_Date
)
RETURN
CountofClients

Thanks.

[Tanushree_Kapse]

Hi @MITHUN_NAIR ,

Try the below measure:

VAR Selected_Date =SELECTEDVALUE ( Date[Date] )

Return CountofClients= CALCULATE( DISTINCTCOUNT( Clients[Client] ), DATESBETWEEN( 'Date'[Date], DATEADD('Date'[Date],-2, DAY), Selected_Date))

Mark this as a solution if I answered your question. Kudos are always appreciated.

Thanks.

[MITHUN_NAIR]

Hi Tanusree,

Thanks for your quick response.

From your code we are getting the data for those dates. But where are we comparing the values based on the condition I mentioned.Need to get those count where the condition satisfies

[Tanushree_Kapse]

Hi @MITHUN_NAIR ,

VAR Selected_Date =SELECTEDVALUE ( Date[Date] )

Return CountofClients= CALCULATE( DISTINCTCOUNT( Clients[Client] ), DATESBETWEEN( 'Date'[Date], DATEADD('Date'[Date],-2, DAY), Selected_Date) ,FILTER ( Clients, Clients[KPI] = "Not Met" || Clients[Quality] = "Not Met" ))

[MITHUN_NAIR]

Hi Tanusree,

Take distinct count of clients only if all the 3 rows satisfies the condition. From the Above data for client A we have all the 3 rows satisfies the condition.So the count result is 1

[Tanushree_Kapse]

Hi @MITHUN_NAIR ,

Mark this as a solution if I answered your question. Kudos are always appreciated.

Thanks.