Not able to create Date table in power BI

[amitnarkar3]

Hi All,

I am basically trying to create toggle between Last Hour, Last 24 Hours, Last 7 days, Last 30 days etc. I joined this table with simple Toggle table which simply list downs range and some ID.

I  created below table in power bi.

Period = UNION( ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date',DATESBETWEEN('Date'[Date],[Max Date]-7,[Max Date]-1)), 'Date'[Date]),"Period",1),

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date',DATESBETWEEN('Date'[Date],[Max Date]-30,[Max Date]-1)), 'Date'[Date]),"Period",2),

ADDCOLUMNS(SUMMARIZE(CALCULATETABLE('Date',DATESBETWEEN('Date'[Date],[StartOfPreviousQuarter],[EndOfPreviousQuarter])),'Date'[Date]),"Period",3),

ADDCOLUMNS(SUMMARIZE(CALCULATETABLE('Date',DATESBETWEEN('Date'[Date],[StartofCurrentYear],[Max Date])),'Date'[Date]),"Period",4),

ADDCOLUMNS(SUMMARIZE(CALCULATETABLE('Date',DATESBETWEEN('Date'[Date],[Last 24 Hour],[Current DateTime])),'Date'[Date]),"Period",5),

ADDCOLUMNS(SUMMARIZE(CALCULATETABLE('Date',DATESBETWEEN('Date'[Date],[Last Hour],[Current DateTime])),'Date'[Date]),"Period",6))

Using this table and toggle table I could filter data as per slicer selection i.e Last Hour, 24 Hour etc.

This worked fine as long as I dont bring in the timstamps in Date table. The moment I start introducing data with time stamps so I can work on Hourly slicer above logic fails with below error

"A Date column containing duplicate dates was specified in the call to function 'DatesBetween'. This is not supported."

How do I fix this? I do need to take timestamp into consideration mostly for Last Hour & Last 24 Hour slicer values.

[v-sihou-msft]

@amitnarkar3

As the error message mentioned, you should use a single date expression as start date and end date in DATESBETWEEN() function. You put a date column like [Max Date] which contains multiple duplicate values. So if you want to filter last 30 days, the filter in calculated table should be like:

DATESBETWEEN('Date'[Date],TODAY()-30,TODAY()-1)

Since you want to filter the last X hours data baesd on datetime, you can use NOW() instead of TODAY(). In DAX, using date/datetime to subtract number is on day level. So to get the last hour, your expression should be like:

DATESBETWEEN('Date'[Date],NOW()-1/24,NOW())

Regards,

[amitnarkar3 · Accepted solution]

Thank you Simon for your response. Max Date points to Today(). I tried doing the same as you suggested calling a function directly. It did not work for me.

I managed using below logic, but I liked the simplicity of earlier logic.

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sheet1',filter( values('Sheet1'[Date]),AND('Sheet1'[Date]<[max Date],'Sheet1'[Date]>[max Date]-8))),'Sheet1'[Date]),"Period",1) --- As Last 7 days and similar logic for others