Power BI Calendar Table: My Favorite Trick! (Offsets/Relative Date Range)

[@olaoyedamilare4006]

Hi Avi. I cannot seem to find the Building the Ultimate Calendar video. Please help

[@paulb1915]

Just had to say this calendar you built is gold!!! Thank you.

[@hanyfouda3249]

Hi Avi, Thanks for great videos I still dont know how to compare Apple to Apple , I wonder which one used is it Curday or CurYear offset

[@shahabali4899]

where can i download the ultimate calender table

[@michealreynolds7498]

Avi, this is a great idea. I am going to have to include that in my next report. I hope all is going well with you.

[@4eyesleo]

Hey! I can understand, how offsets cut off the current year, but have no idea how they cut off the corresponding months of the previous year. How come that the Sept16 cuts of with CurMonthOffset

[@fredbuser9676]

Solid AF

[@demris15]

Great set of videos... im curious if there is a way to add holidays or if that has to be added manually with an external table

[@EricaDyson]

Oh yes. Just what I needed! Thanks so much.

[@joeparks812]

Is it possible to use the Ultimate Calendar Table to factor weekends out of a date range? Does anyone have any suggestions on the best way to do this?

[@kimberlyvassell-jacob2133]

Hi Avi, I was wondering if it is possible to pull the same exact data from 2 different tables with one table having a -1day offset

[@taariqtoffar5823]

Avi, how would i go about grouping offset, for example having a radio button for last 6 months

[@PowerBIPro]

+Taariq Toffar Great question. Just add a Custom Column in the Query Editor based on the CurMonthOffset. So if it's -1 to -6 then 'Past 6 Months'. ⚡Power On!⚡Avi ** Join me for next #TalkPowerBI Live on Fridays www.KZbin.com/PowerBIPro/Live **

[@007MrMad1]

How is the calendar table updated on daily basis? Meaning how will the offset columns will keep Changing daily?

[@bibinbastian01]

This is so smart.

[@alexsyoutube6990]

Super great video, thanks alot

[@PowerBIPro]

Hi Alex Havermans, My pleasure! ⚡Power On!⚡ / Avi ★ Get Your Questions Answered LIVE on TalkPowerBI Fridays www.learnpowerbi.com/talkpowerbi ★

[@thomasrussell758]

Would it be a bad idea to just have the date range of the calendar set as MIN(sales date), MAX(sales date)? Assuming the only dates you were working with was sales transactions?

[@PowerBIPro]

Ha! The debate to Truncate your Calendar (to match your Data) or Not is like Apple vs. Windows :-) I'm firmly on the side of NOT truncating the Calendar. Truncating as you suggest makes some things easier (YTD formulas as they show in reports), but my biggest problem is that inevitably I end up with Multiple Datasets (as the model grows) and it becomes harder to decide the right date range for your model. I find it simpler to keep Full Years only. You could start with Truncated (Mi n/Max approach) if you like and later change it to Full Years when you need. ⚡Power On!⚡Avi ** Join me for next #TalkPowerBI Live on Fridays www.KZbin.com/PowerBIPro/Live **

[@thomasrussell758]

Avi Singh - PowerBIPro thanks for the quick reply! Ha yeah that makes sense and I can already think of other scenarios where it would break (like forecasting). I just need to find some way to limit the range of the date slider so it doesn’t have an end date of December when it’s only March

[@PowerBIPro]

I usually use the Ultimate Calendar (see video link below, you would also be able to download my Ultimate Calendar) ...and use the Future Dates or Month Offset Filters to exclude Future Dates/Months. kzbin.info/www/bejne/jpu5fZSMgdGoj6c ⚡Power On!⚡Avi ** Join me for next #TalkPowerBI Live on Fridays www.KZbin.com/PowerBIPro/Live **

[@RobIvy]

Avi, great stuff. One question. We don't close the month until the 5th business day after the end of the month, so for example on July 3 the last closed month is gong to be May. Thus using CurrentMonthOffset of -1 doesn't work for the first 5 business days of the month. Any ideas?

[@YennLund]

I have the same question, did you ever work it out?

[@MuggsMcGinnis]

I did notice a logic error for his date offset stuff. He wants to be able to compare year-to-date. But, if he treats the current date as "past" then he could be treating last year's complete day to partial data from today. The current date should be "future", so FutureDate = if [Date] < CurrentDate then "Past" else "Future" A small detail but it's important to recognize the small details when doing this stuff.

[@AdnanKashemOxe]

Thanks soo soo oo oo much.. It helped me a lot.

[@PowerBIPro]

Hi Adnan Kashem. My pleasure! Glad this video resonated to you. ⚡Power On!⚡ / Avi ★ Power BI Complete Tutorial: kzbin.info/www/bejne/d3jVnWB-bZyjiLc ★ ★ Subscribe and Join me LIVE for Talk Power BI Fridays kzbin.infolive ★

[@aubreyschroth3703]

Ultimate Calendar download link is not working

[@janwibhattar1168]

After applying the ultimate calendar table, the dates are all jumbled up. Can anyone help me with this?

[@hey_keith]

Hi Avi! Great tutorial! Can you update the Ultimate Calendar Table to include Offset by Weeks? Or provide some guidance on how to manually create fields to include Offset by Weeks so I can update it myself please? Thank you!

[@PowerBIPro]

Keith, head to the downloads page and download the Ultimate Calendar Files again. I updated the table with Week Offsets (uses Sunday as the start of the week). Great question...I loved the exercise (although not sure I got the most elegant solution). I would be posting the video explaining the steps real soon. ⚡Power On!⚡ -Avi

[@hey_keith]

Thank you so much for updating the table with Week Offsets Avi! This Ultimate Calendar Table has catapulted my reports to the next level. I can't thank you enough and I look forward to your next videos!

[@vishnu4486]

can u explain how you created calendar table ?

[@leonardo7179]

Hi Avi, excellent tutorials! I sum myself to the above request. Couldn't find the video explaining how you built the calendar.

[@TonyChangTaiwan]

In commercial world, we call "YTD-YearToDate" (Past) and "YTG-YearToGo" (Future)

[@Mofasofa2012]

Hello Avi. Great Video. Why did you use the "Future" "Past" solution for the days instead of the Offset "is less than 0" solution as for years/months/quarters?

[@PowerBIPro]

Michael Eisele, I used Future/Past, as that can be easily used in a Slicer (if I ever need to use it that way).

[@janwibhattar1168]

Where is the video for how you build the ultimate calendar table?

[@rrrprogram8667]

Yee.. first like .. first comment :D