cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
amitnarkar3 Regular Visitor
Regular Visitor

Not able to create Date table in power BI

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.

1 ACCEPTED SOLUTION

Accepted Solutions
amitnarkar3 Regular Visitor
Regular Visitor

Re: Not able to create Date table in power BI

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

View solution in original post

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Not able to create Date table in power BI

@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 Regular Visitor
Regular Visitor

Re: Not able to create Date table in power BI

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

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors