I'm collecting the alternative templates that people mention in the video description - if you're feeling adventurous, you can try them all and see which is your favorite! 😄
Been waiting for this video, I hired a powerbi helper a few months ago and he told me how important it was to have a date table but now that I am in a new role I forgot what he had me do and this video saved me from paying him $200 to explain it to me again.
I don't get to play in PowerBI as much as I used to, but this was such a great refresher on date table dynamics. They used to flummox me to no end. Bookmarked for future replay!
@@brianjulius6401 Oh hey Brian! Nice to see you here :) Now I really need to check it out. I think I'm a die-hard Brian Grant fan by default, just pawing at one of his reports really changed the way I work. I wish it were easier to share things like that, because it's so helpful to see how other people build things - but then we're all always working with confidential data so there's never an easy way.
This video is so helpful! Thank you! I work in a grants department where our funder awards have varying start and end dates within a year. Our finance department wants to see this revenue in relation to calendar year. Any suggestions? The total award and start/end dates are in my query. I have created columns for number of months covered for each grant award period and divided that by total award for the value each month, but having trouble managing the individual months that fall within the date range and relating it to the date table for the regular calendar year.
That's a tricky one - in finance, they typically handle this by dividing the amount by month or day like you're doing, but they unpivot it so that there's one row per month (or day, if your dates overlap months). It's a lot easier if your finance CRM handles it for you, because its a bit of a heavy lift on the PQ side if you have a lot of data. I've used this super obscure technique in the past where I've had to DIY it (for sales contract data, but concept sounds very similar) and it worked better than I expected: www.encorebusiness.com/blog/power-bi-how-and-why-to-add-records-between-the-start-and-end-date/ A "bridge table" can help bridge the gap between the months and days in the date table, but if you're pulling it down to the amount per day level then you may not need to use one--
Very helpful content - I would be curious if you have any thoughts around the date calendar scenario where you want to switch back and forth between fiscal / calendar year and maintain drill hierarchy and sort order. Struggling with this.
I think you'd have to use bookmarks, one for fiscal year and one for calendar year, each linked to their own visual (show one and hide the other with the bookmark, each visual has its own hierarchy). One of the very first videos I did goes through how to do this - not using date axis, but similar concept with maintaining sort on field parameters: ruclips.net/video/Nt3QgZrJXfY/видео.html
@@bi-ome Thank you for your response! I went down a rabbit hole on this and still haven’t figured it out. Goodly, PowerBI Helpline, Data Logic with Shivham and the DataZoe blog all cover the FY/CY switch with limited solutions but none of them seem to work for the following scenario: Visual (Stacked Bar Chart) that has a Date hierarchy with: Year, Quarter, Month Calendar Selection (single select) - Fiscal - Standard Year Slicer (dropdown) Quarter Slicer (dropdown) Month Slicer (dropdown) Requirements: 1. Visual does not reset on calendar selection toggle. (Parameter strategy alone seems to reset on selection despite editing interactions and the Union strategy between 2 date tables does not reset visual on toggle, but presents sorting issues.) 2. Data syncs and stays at same hierarchy on Calendar Selection toggle 3. At the year hierarchy level- only one bar is shown (not 2 split between the fiscal year) 4. At the quarter hierarchy level- quarter axis labels change appropriately based on calendar selection. 5. At the month hierarchy level - month sort order is maintained with appropriate starting month based on calendar selection while maintaining the proper Quarter labels above. I tried your suggestion with parameters + bookmarks. Got super excited but then was running into limitations around the advanced slicer sync (to sync data between the FY set of 3 (year, quarter and month) and SY set of 3. Also having a separate issue with the drill-down hierarchy. I will post again on the Power Bi forums
QUESTION: How to handle a specific date-column that contains NULL values? Hello Christine, thanks for the great video! Especially the tips regarding inactive relationships! My Situation: I do have a column "Completion Date", which has 60% valid dates (in the past), and 40% NULL values, as this tasks are not completed yet! This should also be a 1:1 relationship, but Query Editor says "Not allowed, as multiple NULL values are found, and proposes a 1:n relationship" Question: How to handle these situation? (Would 1:n be OK, need to change type of column, ...??) Thanks for your advise, Katrin
It won't be 1:1 - you could have two tasks with the same completed date, even if you don't currently, so you want it set accordingly. The date table side will be 1, the task side will be many. The null values are fine, you'll just want to add a filter to remove them on visuals where you're grouping by month/year/whatever or they'll show up in a (Blank) category!
I have a data set that refreshes nightly with the last 90 days worth of data. The hard coded start date results in a lot of unnecessary dates in the table. How is that best handled automatically?
When you use the field parameters for dynamic date axis buttons, can you limit the number of columns shown? Say I have 5 years of data. When the year option is selected, I would want to see all 5 years. However, when the month option is selected, I would only want to see the last 15 or so months. Is there a way to do this? Thanks! I found your video very helpful!
You’d need to use bookmarks. The bookmark navigator can be made to look like a field parameter slicer, but would be changing filter setting on the chart and selecting field parameters in a hidden visual with the bookmark. I did a video that’s kind of related to it here a ways back, you could adapt this sort of thing to work (it’s not exactly the same but similar enough) ruclips.net/video/Nt3QgZrJXfY/видео.htmlsi=0KbXIwXL-Sx1C04p
I'm collecting the alternative templates that people mention in the video description - if you're feeling adventurous, you can try them all and see which is your favorite! 😄
What about the date table you can use from the Bravo tool?
@ I hadn’t heard of that before, I will check it out - the premise is intriguing!
@bi-ome cool. It gives you some nice setup options
Been waiting for this video, I hired a powerbi helper a few months ago and he told me how important it was to have a date table but now that I am in a new role I forgot what he had me do and this video saved me from paying him $200 to explain it to me again.
😂 Glad it helped!
I don't get to play in PowerBI as much as I used to, but this was such a great refresher on date table dynamics. They used to flummox me to no end. Bookmarked for future replay!
Thanks, Ron! I hope you are doing well :)
Month start date reformat..... *Chef's kiss*
New sub gained for that alone Christine.
Love it, Keep up the awesome work.
Best date table I've seen. Thanks for sharing, Christine. 😊
Literally needed a guide like this today. Thank you so much for creating this!
😄 That’s great to hear!
Great video, Christine!!
Thank you! Glad you liked it!
Great video. Thanks Christine👍❤
Great information Christine
awesome video, jam packed with info
So helpful ... thanks for sharing and explaining.
5:01 'Miniature lame date tables'. Lol, that cracked me up.
I had a longer rant on it but cut it out, it’s hard to reign it in sometimes 🤣
Melissa D. Corte - EDNA's *DATE Table* has been the most refined and polished one I've used so far.BTW better to know alternatives.
Thanks for sharing! Maybe we can collect a list and do a comparison :)
+1 on Melissa’s Extended Date Table being the GOAT.
@@brianjulius6401 Oh hey Brian! Nice to see you here :)
Now I really need to check it out. I think I'm a die-hard Brian Grant fan by default, just pawing at one of his reports really changed the way I work. I wish it were easier to share things like that, because it's so helpful to see how other people build things - but then we're all always working with confidential data so there's never an easy way.
@@brianjulius6401 I got to know Melissa's one through Brian's Knowledge hub 🙏.Such an expert !!
This video is so helpful! Thank you!
I work in a grants department where our funder awards have varying start and end dates within a year. Our finance department wants to see this revenue in relation to calendar year. Any suggestions? The total award and start/end dates are in my query. I have created columns for number of months covered for each grant award period and divided that by total award for the value each month, but having trouble managing the individual months that fall within the date range and relating it to the date table for the regular calendar year.
That's a tricky one - in finance, they typically handle this by dividing the amount by month or day like you're doing, but they unpivot it so that there's one row per month (or day, if your dates overlap months). It's a lot easier if your finance CRM handles it for you, because its a bit of a heavy lift on the PQ side if you have a lot of data. I've used this super obscure technique in the past where I've had to DIY it (for sales contract data, but concept sounds very similar) and it worked better than I expected: www.encorebusiness.com/blog/power-bi-how-and-why-to-add-records-between-the-start-and-end-date/
A "bridge table" can help bridge the gap between the months and days in the date table, but if you're pulling it down to the amount per day level then you may not need to use one--
Very helpful content - I would be curious if you have any thoughts around the date calendar scenario where you want to switch back and forth between fiscal / calendar year and maintain drill hierarchy and sort order. Struggling with this.
I think you'd have to use bookmarks, one for fiscal year and one for calendar year, each linked to their own visual (show one and hide the other with the bookmark, each visual has its own hierarchy). One of the very first videos I did goes through how to do this - not using date axis, but similar concept with maintaining sort on field parameters: ruclips.net/video/Nt3QgZrJXfY/видео.html
@@bi-ome Thank you for your response! I went down a rabbit hole on this and still haven’t figured it out.
Goodly, PowerBI Helpline, Data Logic with Shivham and the DataZoe blog all cover the FY/CY switch with limited solutions but none of them seem to work for the following scenario:
Visual (Stacked Bar Chart) that has a Date hierarchy with:
Year, Quarter, Month
Calendar Selection (single select)
- Fiscal
- Standard
Year Slicer (dropdown)
Quarter Slicer (dropdown)
Month Slicer (dropdown)
Requirements:
1. Visual does not reset on calendar selection toggle. (Parameter strategy alone seems to reset on selection despite editing interactions and the Union strategy between 2 date tables does not reset visual on toggle, but presents sorting issues.)
2. Data syncs and stays at same hierarchy on Calendar Selection toggle
3. At the year hierarchy level- only one bar is shown (not 2 split between the fiscal year)
4. At the quarter hierarchy level- quarter axis labels change appropriately based on calendar selection.
5. At the month hierarchy level - month sort order is maintained with appropriate starting month based on calendar selection while maintaining the proper Quarter labels above.
I tried your suggestion with parameters + bookmarks. Got super excited but then was running into limitations around the advanced slicer sync (to sync data between the FY set of 3 (year, quarter and month) and SY set of 3. Also having a separate issue with the drill-down hierarchy.
I will post again on the Power Bi forums
Fiscal year option is great we start ours in October which is a massive pain
QUESTION: How to handle a specific date-column that contains NULL values?
Hello Christine, thanks for the great video! Especially the tips regarding inactive relationships!
My Situation:
I do have a column "Completion Date", which has 60% valid dates (in the past), and 40% NULL values, as this tasks are not completed yet!
This should also be a 1:1 relationship, but Query Editor says "Not allowed, as multiple NULL values are found, and proposes a 1:n relationship"
Question: How to handle these situation? (Would 1:n be OK, need to change type of column, ...??)
Thanks for your advise, Katrin
It won't be 1:1 - you could have two tasks with the same completed date, even if you don't currently, so you want it set accordingly. The date table side will be 1, the task side will be many. The null values are fine, you'll just want to add a filter to remove them on visuals where you're grouping by month/year/whatever or they'll show up in a (Blank) category!
@@bi-ome Hello and thank you for your fast reply! So 1:N is OK, perfect. Looking forward seeing your next tutorials, BR Katrin
I have a data set that refreshes nightly with the last 90 days worth of data. The hard coded start date results in a lot of unnecessary dates in the table. How is that best handled automatically?
You can use relative date filters in Power Query for this! It shows up in the filter menu for the date fields.
When you use the field parameters for dynamic date axis buttons, can you limit the number of columns shown? Say I have 5 years of data. When the year option is selected, I would want to see all 5 years. However, when the month option is selected, I would only want to see the last 15 or so months. Is there a way to do this? Thanks! I found your video very helpful!
You’d need to use bookmarks. The bookmark navigator can be made to look like a field parameter slicer, but would be changing filter setting on the chart and selecting field parameters in a hidden visual with the bookmark. I did a video that’s kind of related to it here a ways back, you could adapt this sort of thing to work (it’s not exactly the same but similar enough)
ruclips.net/video/Nt3QgZrJXfY/видео.htmlsi=0KbXIwXL-Sx1C04p
thanks