This video was INSANELY helpful. I wanted my between date slicer to always have the full range of data and when I'd refresh the data the max date was stuck on the old max date from when the report was published. This solution worked great and now my date slicer is dynamic. Subbed!
Hi Reid. Just discovered, you can also create the range of dates for the calendar table with: = {Number.From(#"Earliest Sales Date (SOY)")..Number.From(#"Lastest Sales Date (EOY)")} and = {Number.From(#"Relative Date Start")..Number.From(#"Relative Date End")} vs. using the List.Dates() function. Just another way to accomplish the goal. Do you see any advantage to one method vs. the other? Thanks for the inspiration to learn about this topic. Thumbs up!!
I think they're both good approaches! I do like the List.Dates since that at least shows you the calendar parameters and layout provided by that command originally, before you customize it with Dynamic ranges.
It's good, but still creates a lot of unnecessary data - I don't need all days within these 2 years, I only need the days that exist in the Fact table. Any idea how to do that?
Hi Paulina, I'm curious how you define "lots of unnecessary" data? This table only adds a few thousand rows, and less than a MB to the file size. Compared to fact tables with millions or tens of millions of rows of data. With any calendar table, or calendar adjacent table the date ranges need to be contiguous. Especially when working with any time intelligence functions in DAX such as Date Add, Date Diff, etc. So you wouldn't want gap date ranges. You'd want a complete date range between the min and max dates you'd want to filter your fact table by. Hope this helps!
Hi is it ok that query folding does not work with the date table in this example. Not sure if query folding works creating a date table in another way.
There shouldn't be a need for query folding and optimization of a calendar table that is only loading a few thousand rows. As long as the query doesn't take long to run it's fine :)
Hello. Thank you for the video. I have a question to ask you. What part should I adjust to make a date table that has a week interval from the start date to the present date?
Could you elaborate on the question? Are you wanting to create a column on your calendar table? Does this apply to the dynamic date range outlined in this video?
Great help...When i attempt to do the Relative dates, I get the error "Expression.Error: We cannot convert the value #datetime(2020,1,1,0,0,0) to type Date. Details: Value=1/1/2020 12:00:00 AM Type=[Type]
Please slow your speed its a humble request. I know you will say slow your speed from youtube but the rythem is not good while watching video. it would be good if you slow your speed.
Hi Steve, RU-vid actually has a great feature that lets you control the speed of the video. If you’d like to listen to me at a slower speed. You can find the speed adjustment in the lower right with the gear icon 🙂
