Not in the last 3 days

[MalOSB]

Hi,

I am trying to create a report to show sites that have not triggered an alarm in the last 4 days. The software SQL database holds all activities in a single Events Table. I created a table in PowerBI with the Site name and then the Created field showing the latest date, filtered so that only Alarms are showing. Then I need to only show the sites that have the last alarm older than 3 days, however, the date relative filters on dates are only for “in the last”, “is in this” or “is in the next”. Not the “not in the last” that I need. We have 1000’s of sites so I need to filter to only show the sites that have not generated an alarm.

Example table

SiteName	Latest Created
Leeds	01/02/2021 06:10:57
Manchester	01/02/2021 15:32:27
Liverpool	01/02/2021 06:49:53
Glasgow	01/02/2021 04:37:55
London	21/01/2021 09:00:03
Watford	30/01/2021 06:54:32
Portsmouth	01/02/2021 04:10:35

Looking online I found the following suggestion,

IF(Events[Created]>TODAY() && Events[Created]<TODAY()-3,1,0)

But this does not work, returning “0” for all sites. I believe because I need to filter for it to look at only the Alarms in the event table. I have tried to add a Filter but after spending a few hours looking online and fiddling with Filters in Dax I cannot get it to work.

Events Table

Events[Created]

Events[EventTypeID] – All alarms are 1, patrols are 2 ect..

GroupID – Link to site details table

Thank you for any help you can give.

Malcolm

[ryan_mayu · Accepted solution]

@MalOSB 

There is something wrong with the logic of your formula. It is impossible that a date is bigger than today and smaller than past three days.

have you tried below DAX?

Column = if('Table'[Latest Created]<today()-3,1,0)

 

[amitchandak]

@MalOSB , Create measures like

has trigger in last four days= calculate(countrows(Table), filter(Table, table[Latest Created] >=today()-4))

no trigger in last four days= if(isblank[has trigger in last four days],1,0)

has trigger in last four days sites count = countx(values(Table[SiteName]),if(isblank[has trigger in last four days],1,0))