Distinct count rows based on condition

[Elisa112]

Hello Experts 

I am trying to find the count of rows in a table for this month and last month based on a condition but I am getting incorect figures.

Calls this month:

Client Appointed = CALCULATE(

        DISTINCTCOUNT('Calls'[Call ID]),

        FILTER('Calls',MONTH('Calls'[Call Date])=MONTH(TODAY()) && 'Calls'[Outcome] = "appointed"))

I also need to find appointed calls last month

any suggestions greatly appreiated, thanks in advance

[grazitti_sapna · Accepted solution]

Hi @Elisa112,
Please refer to the DAX calculations below for the current and previous month's call volume:

Current Month Calls:
Calls in Current month =
CALCULATE(
DISTINCTCOUNT('Calls'[Call ID]),
FILTER('Calls',
MONTH('Calls'[Call Date]) = MONTH(TODAY()) &&
YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
'Calls'[Outcome] = "appointed"
))

Previous Month Calls:

Calls in Previous month =
CALCULATE(
DISTINCTCOUNT('Calls'[Call ID]),
FILTER('Calls',
MONTH('Calls'[Call Date]) = MONTH(TODAY()) - 1 &&
YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
'Calls'[Outcome] = "appointed"
))
I believe this will achieve the desired outcome.

Thank you

[Elisa112]

thank you!

[Elisa112]

Hello

thank you but I am still getting incorrect figures, with the condition (appointed) I am getting the same figures without the condition.

any ideas?

thanks

[rajendraongole1]

Hi @Elisa112 -Can you please check the below logic.month(today()) will works.

Calls This Month =
CALCULATE(
DISTINCTCOUNT('Calls'[Call ID]),
FILTER(
'Calls',
MONTH('Calls'[Call Date]) = MONTH(TODAY()) &&
YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
'Calls'[Outcome] = "appointed"
)
)

Hope this helps.

[Elisa112]

Hello

thank you but I am still getting incorrect figures, with the condition (appointed) I am getting the same figures without the condition.

any ideas?

thanks

[rajendraongole1]

Hi @Elisa112 -if possible, can you please share the working data pbix file by removing the sensitive data or sample data in excel copy for reference.

Calls This Month =
CALCULATE(
DISTINCTCOUNT('Calls'[Call ID]),
'Calls'[Outcome] = "appointed",
MONTH('Calls'[Call Date]) = MONTH(TODAY()),
YEAR('Calls'[Call Date]) = YEAR(TODAY())
)