DAX relative time function to count number of instances between specific times

[Anonymous]

Hi, 

I have some data in the following format: 

Incident Number	Date Occurred	Incident Name
143	1/1/2001 12:00	ex 1
361	1/2/2001 14:00	ex 2

I am trying to count the number of incidents that have a [Date Occurred] within a certain time frame relative to the current date. I want the number of incidents with a [Date Occurred] between 7 days ago after 12:00 PM to today before 12:00 PM. 

To give an example: if it is Thursday today, I would like to count the number of incidents with a [Date Occurred] between LAST thursday after 12:00 PM and 12:00 PM this Thursday (today). 

Ideally, I would like a measure to get this count. 

Thanks!

[AlB]

Hi @Anonymous 

Try this measure in a card visual:

Measure =
VAR Today12_ =
    TODAY () + ( 12 / 24 ) //Add 12 hours
VAR Beginning_ = Today12_ - 7 // Subtract 7 days
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        Table1[Date Occurred] >= Beginning_,
        Table1[Date Occurred] < Today12_
    )

[Anonymous]

I think you should have 2 columns, which would probably make this problem easier and secondly, it's best practice anyway: one column should store the date part and another should store the time part.

Then it would be much easier to move through dates without having to fiddle with time.

Best
Darek