I always have a date table in my SQL data warehouse, which I can then ingest into whatever platform I am using for reporting. I am going to be setting up a Fabric DWH in the near future, which will also make use of this table, along with other common tables (such as lookups, staff info etc).
I like adding 2 additional columns is_month returns current month, previous month or month name and is_today returns today, yesterday or date. This allows me to set the power bi slicer to current month without the need to update the slicers each month
First, thank you for your videos, they've been so helpful to me as I get started in Fabric! Very timely video... I was just working on this same solution over the past few days. I completely agree with your reasoning for why this should be done in a table instead of just in PowerBI. I would love to hear you follow up on a few complaints/questions I have after going through this process: 1.) I started by writing SQL like you did but you can't generate a table in a Lakehouse via SQL, so I moved to notebook code instead. I like the idea of a shortcut from Warehouse back to Lakehouse as a workaround, but this seems to violate the Medallion architecture pattern. thoughts? Seems like MS should really add the ability to create tables via SQL in lakehouse to better support this type of scenario. Not sure why it can be supported by notebooks but not the sql interface. 2.) I'm fairly new to PowerBI (much more experience with Tableau) and the way PowerBI handles dates just seems absurd to me. Tableau would automatically generate all date hierarchies even in published data sources (equivalent to connecting to a semantic model). The fact that I have to manually create hierarchies for any date I want to use is a real pain. Thoughts on why MS went this route? What benefit does it have over Tableau's implementation? 3.) One thing I didn't realize at first when setting up these hierarchies is that the bottom of the hierarchy needs to be a Date type field in order to get the sorting to work by default. I initially tried Year#-Month#-Day# and this resulted in defaulting to ordering by numeric values descending.
I'm still fighting with my boss about upgrading power bi PRO to Fabric licenses, can't wait to try all this out!
I always have a date table in my SQL data warehouse, which I can then ingest into whatever platform I am using for reporting. I am going to be setting up a Fabric DWH in the near future, which will also make use of this table, along with other common tables (such as lookups, staff info etc).
Any organisation should have a chief date officer !
I like adding 2 additional columns is_month returns current month, previous month or month name and is_today returns today, yesterday or date.
This allows me to set the power bi slicer to current month without the need to update the slicers each month
Yup. That is a great solution.
First, thank you for your videos, they've been so helpful to me as I get started in Fabric! Very timely video... I was just working on this same solution over the past few days. I completely agree with your reasoning for why this should be done in a table instead of just in PowerBI. I would love to hear you follow up on a few complaints/questions I have after going through this process: 1.) I started by writing SQL like you did but you can't generate a table in a Lakehouse via SQL, so I moved to notebook code instead. I like the idea of a shortcut from Warehouse back to Lakehouse as a workaround, but this seems to violate the Medallion architecture pattern. thoughts? Seems like MS should really add the ability to create tables via SQL in lakehouse to better support this type of scenario. Not sure why it can be supported by notebooks but not the sql interface. 2.) I'm fairly new to PowerBI (much more experience with Tableau) and the way PowerBI handles dates just seems absurd to me. Tableau would automatically generate all date hierarchies even in published data sources (equivalent to connecting to a semantic model). The fact that I have to manually create hierarchies for any date I want to use is a real pain. Thoughts on why MS went this route? What benefit does it have over Tableau's implementation? 3.) One thing I didn't realize at first when setting up these hierarchies is that the bottom of the hierarchy needs to be a Date type field in order to get the sorting to work by default. I initially tried Year#-Month#-Day# and this resulted in defaulting to ordering by numeric values descending.
You're brilliant!!!! Always learn something new, and get great ideas from you sharing your knowledge. Thanks so much!!
Awesome to hear! Makes the work worth it.
You're the G P 🤜
Love your vid/teaching sessions. But... the sound recording has become muffled and echoey for you last 2 or 3 vids.
Don't know if anyone's noticed, but the certificate for the GIAC website has expired.
Thanks for letting us know. Someone is on it. :)