topic If Due Date is 3 days away in DAX Commands and Tips

If Due Date is 3 days away

richpow — Sun, 02 Jan 2022 10:09:29 GMT

I have a dash report that looks at progress of complaints. Each section of the complaint has a due date. for example, if the entry is deemed a complaint then an acknowledgment is due within 3 days and a response within 28 days. I have added measures for these.

Date logged: 2/1/2021

Acknowledgement Due (Date logged plus 3 days) = 5/1/2021

Is a complaint: Yes or No

Date Due: (if complaint is yes add 28 days, otherwise it stays blank) = 30/1/2021

What I am trying to do is create a measure that would determine if the Due Date (Future date) is within 3 days of today and the Status is not "Closed" then Mark it as "At Risk" and then with Power automate send an email. I'm just stuck on how to create the measure or custom column that can count if the due is within the next three days and the status isn't marked as closed.

Re: If Due Date is 3 days away

speedramps — Sun, 02 Jan 2022 11:51:53 GMT

Conisder this solution

Click here to download PBIX

Create a fact table with

Case ID
Date logged
Is complain (Y/N or blank)
Status (Closed or blank)

Create parameter table with one record

'Calendar parameters'[Current date])

Create this DAX measure

Alert =

Var Mystatus = MIN(Facts[Status])

Var Iscomplaint = MIN(Facts[Is complaint])

VAR daysage = DATEDIFF(SELECTEDVALUE(Facts[Date logged]), MAX( 'Calendar parameters'[Current date]), DAY )

RETURN

SWITCH(True(),

-- if case is closed then dont send an alert

Mystatus = "Closed", "",

-- if case is a complaint and is over 28 days old then send an response overdue alert

Iscomplaint = "Y" && daysage > 28,"Response overdue",

-- if case is over 3 days old and not had an acknowledgement then send an response overdue alert

Iscomplaint = "" && daysage > 3,"Acknowledgement overdue",

)