cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Michaelbean21
Frequent Visitor

FILTERING A DATE TABLE BASED ON MIN AND MAX VALUES

I am trying to create a filtered date table based on min and max values which are set to data type "Date/Time".

Michaelbean21_1-1657124757112.pngHowever I am getting an error telling me that "an invalid numeric representation of a date value was encountered". 
But my "Date" in my Complete Date table is also set to data type "Date/Time".

So I have no values to my knowledge that are not data type "Date/Time".

Does anyone know why this happenning?

 



2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Michaelbean21 

 

You can try the following method to create a date table using the maximum and minimum values.

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

vzhangti_0-1657531343038.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

PaulDBrown
Super User
Super User

Best practices recommend splitting DateTime values into two separate dimension tables: one for Date; the other for time.
DATESBETWEEN also requires date expression for the parameters:

datesbetween.png

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors