Duplicate Dates Using DATESBETWEEN

[GeekyT]

Hi,

I have a database of work orders and I'm trying to pull a count of those which have a Complete by Date in the next 7 days and are in the status CRE or CIP. Here is my DAX:

YELLOW1 = calculate(count(wkaw[Work Order]),DATESBETWEEN(wkaw[Complete by Date],TODAY(),TODAY()+7),filter(wkaw,OR(wkaw[status]="CRE",wkaw[status]="CIP")))

However I get this error:

This report has been working for months without this error but when my collegue when to run it today, as they do every Monday, suddenly one of the visuals is giving this error. I originally thought it is related to the clocks going forward an hour over the weekend (were in the UK) but this visual looks forward, not back, so that shouldn't be it. 

On another message string on this forum I saw a similar issue which was resolved by referring to a Date Table which I would have to create rather than the Complete by Date column. But when I try to do that using the CALENDAR function, I get the same error. I also tried to use the DATESINPERIOD function but also got the same error.

Can anyone direct me as to how to remove this error please?

Thanks everone.

[amitchandak · Accepted solution]

@GeekyT , You should always use Date/Calendar table marked as Date table

example

Rolling 7 = CALCULATE(count(wkaw[Work Order]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),7,day))

or

Rolling 7 = CALCULATE(count(wkaw[Work Order]),DATESINPERIOD('Date'[Date ],today() ,7,day))

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

[GeekyT]

@amitchandak - ah I see, I tried to create a date table but I was basing it on the Complete by Date from my database which contains non unique dates which PBI doesnt like. I've created one from scratch now and it's working fine. Thanks for the help.