active at certain times

[mik618]

Hi there, 

I've been asked to do various reports looking at improvment and display this in a graph, one report is looking at confirming appointments, every appointment should be confirmed the date the appointment is made, so there is a person column, appointment ID, appointment date, appointment confirmed date columns;

Patient	appointment_ID	Appointment_Date	Appointment_Confirm
1	1000	01/01/2020	01/01/2020
1	1001	01/02/2020	03/04/2020
2	2001	01/03/2020	01/03/2020
3	2050	01/03/2020	null
4	2075	01/03/2020	10/03/2020

I want a formula that will show in a graph the unconfirmed appointments by date, so for 01/03/2020 there would be 3 unconfirmed appointments. 

Really struggling with the DAX to get this right so any help would be greatly appreciated

Thanks 

[amitchandak · Accepted solution]

@mik618 , Refer article on a similar topic. The current employee is you pending confirmation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[mik618]

Hi @amitchandak 

Thats exactly what I am after, thanks!

[jdbuchanan71]

Hello @mik618 

Wouldn't it be 2 unconfirmed on 1/3?  Appointment 2001 the dates match.  Anyway, something like this should get you the count of rows where it doesn't match.

Unconfirmed Count = 
COUNTROWS(
    FILTER (Appointments, Appointments[Appointment_Date] <> Appointments[Appointment_Confirm])
)

[mik618]

Hi @jdbuchanan71 

Thanks for helping, it would be 3 unconfirmed at this date as id 1001 would still be outstanding, thats the problem I'm having it needs to be able to look at all unconfirmed appointments, not just the appointment dates, so we can look at any date in the last year or so and see how many were outstanding at that time. 

Thanks 

[jdbuchanan71]

@mik618 Is your table only the unconfirmed appointments?  If so you can do a COUNTROWS (YourTable), pull the appointment date into the chart and the measure into the values and see the unconfirmed appointments by date.