Forum Discussion
Duplicate Dates Using DATESBETWEEN
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:
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.
Anonymous , 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
2 Replies
Anonymous , 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
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.