How to get a DATE TABLE easily in Power BI
Vložit
- čas přidán 30. 07. 2024
- Do you use a central date table for your Power BI datasets? There is a LOT of POWER in having one. Adam shows you different ways to create one including a really simple easy option that's BANANAS!
REDUCE Power BI dataset size by 60% with ONE CHECKBOX???
• REDUCE Power BI datase...
Bravo for Power BI
bravo.bi
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Dates #GuyInACube - Věda a technologie
The channel deserves way more subscribers. I was introduced to the channel more than an year ago by a colleague and this has been my go to channel ever since. Super informative! Thank you!
Man, I love your energy and your breadth of knowledge. I was a teacher in a previous life, so I'm self-teaching a lot of this in a new manufacturing analyst role. I share this to let you know you're an awesome teacher! Thanks for doing this!
I built our Date dimension from the sqlBI Date template last year. I love how they make it easy for a new user.
This is bananas!!!! I cannot wait to download this when I get to work tomorrow!
Bravo!
Thanks Guy, Thanks Marco Russo
I knew it, but
I didn't use to now, but with your EXCELLENT explanation, I going to use it!
Thanks a lot!
gab
Rio Cuarto, Argentina
This is awesome. I've been meaning to try Bravo, but was reluctant to do so for some reason. Now, I can't wait to get back to work and clean up my time intelligence within a couple of the models I've built for my org. Keep up the great work!
Question. All of my tables are direct query with refresh set to hourly. Once I added the date table via Bravo it eliminated the option for hourly refresh. What is the fix for this? I'm assuming I did something wrong but perhaps it's software limitation?
Thank you so much for everything you guys do. I've been watching you guys for a while and it has helped me a lot in my new role as a reporting analyst. Came across this video and had a question. I am trying to build an attendance report for work and I was wondering with dates if there was a measure or an if statement to have 2 days in a row to = 1 day. Is this possible?
Thanks for the update.
I installed bravo self contained but when I clicked on it from Bi its completely blank. What did I not do right please?
I am struggling with DAX function in creating Date Dimension.
That's amazing Adam ...... That's (Bravo) new for me ...... Great way to create calender table indeed ....Guy in a cube channel always comes with something new n interesting ..... Thank you so much for making easy for us...
Hi Adam, you have no idea how much this video has saved my life!!!! 🙂
Absolutely loved this thanks.
I love this but I have a question, maybe I missed it; is there any way to remove months/weeks/days/ from the filters that don't have data? Once you have a date/calendar table and use relationships, the filter will show all months in that table, regardless if there is data present. Thanks in advance!
Fantastic tool!🦾Thanks for sharing👍
How can I to a left outer join using the date table made using Bravio?
Yes I needed a calendar table that included holidays. Great solution
Thanks, that is a great tool!!
Newbie here still Learning your channel is 🔥 by the way is this what I would use if I want to take two columns that have start date and end date but I need to have a a column that determines the total years. Currently someone set it up as a measure but I don’t think you can use the measure bc when you use filters it’s showing balance when filtering to zero! Actually I have one date column but I need to calculate from that date column how many years as of today but what if some of the dates are blank?
is there a way to keep only last day data on the table but past 2 weeks line chart next to it on the same tab in power bi? This way i can see what is the ranking list as of today and how was the stats changing day by day on the chat for each ranked items past 2 weeks?
Is it possible to use this calendar to sum dates for sales that span a time? So we have a sale total of 10,000 that spans 4 weeks, can we sum that 2500$ a week just for those weeks but no other weeks?
Thanks man. you solved my problem.
This is going to save so much time and effort. Our team just took over a few datasets from another team that was doing their own thing. One has 9 different dates in the main data table, and then each of those dates has an additional 8 columns for day, week, month, quarter, year, year month, year quarter, day of year. In addition, Auto date/time was turned on... Performance is terrible.
Deleting all of these columns is going to feel so good.
Hello,
I have a small doubt.
I am new to the Powerbi tool but I am well versed with data visualization and analysis.
My question is "why" another date table ??? What is the REASON behind creating a separate date table ??
I use other tools for creating reports. Suppose i have sales data with products and order_date. I want to slice the data or try to create a chart showing the total products based on year, month, week etc, we can achieve this by extracting the year, month or week from the existing sales date column as well. We can make this as a new column in the sales data itself.
What is the significance of creating a different date table altogether ?
Thanks Adam!
Never heard of Bravo, but this is a game changer! Congrats to the DEV team and thanks Guy in a Cube 👌🙌
The kudos goes to the SQLBI team 👊
that's just Amazing
Thank you!!!
Do you have data samples we can use to incorporate or follow your videos?
Good one thanks
Nice!!! I'll use it after Easter break 😬
Amazing, 👏👏
Bravo👏
Automatic Period Intelligence???? SWEET!!! Glad I watched this video. Thanks for making it.
Bravo! ❤️
How is it that you guys are NOT at 1M subscribers!!!!!! This was an amazing video and Bravo is Brilliant!!!!!
Very Impressive.. 🙏
That is super cool thanks gents
Great Video!
Great video. Bravo is awesome! Btw. nice t-shirt, inyalowda.
As a PBI noob, I like using M code, so although setting up a Calendar in DAX seems easier, if you do it via DAX directly or via Bravo, the Calendar isn't available for M code. Is that true? That's the impression I've got - that all the Power Query stuff happens in PBI before the DAX staff.
What if my Ship Date also has Time and I'd like to use Time also?
Thanks.
I didn't know about Bravo until now! I'm curious if the time intelligence wizard will work on custom calendars...
Technically it could (and it will). The current (beta) version only supports the Date table created by Bravo. However, the underlying library (DaxTemplate on GitHub) can support any custom calendar, once the key columns are "decorated" with custom attributes. We'll create documentation and probably additional features to support that. Stay tuned!
This video is bananas.
As always, top info. Though I would've liked to understand the underlying details of how the date range from ShipDate was different from OrderDate. I couldn't really understand why it would show data from February and March when filtering for March.
Because it was the Order Date that was joined to the calendar table. So Month under the ship date really meant all the items ordered that were shipped in March. Some of the items shipped in March had order date in Feb.
@@fredbarnes196 ah thanks!! Now it makes sense
This saved me from countless wasted hours... My friend - I thank you.
Hi Adam! Please help me understand in your Dax Measure how you selected the date range (2011 - 2022) - The plants I'm working on will be going on forever as long as Coca-Cola exists lol - Do I have to change this yearly)
can't download bravo. Dunno know why
great video, and Bravo can probably speed things up, however, doing that Interval still has to do the calculations -> increased load on the capacity. but great explanation of the concepts. (even without Bravo)
Bravo is an amazing tool! But I didn't get the benefits of a date table clearly from your explanation. Maybe there is some technical consens among IT people to prefer a date table. But for business people the chart shown around 2:22 correctly answers the question: When were the orders placed that we shipped in March? Now with your date table solution, the slicer implicitly changes roles between order date and ship date, depending on what measure you look at. For me, this doesn't reduce confusion, it increases confusion. Plus the business people can't get an answer to the question above anymore.
Take a look at this video, which explains in more detail how to manage that situation and pros/cons of the two possible approaches.
czcams.com/video/tBr6IFmmvOE/video.html
bravo Rociante
May the 4th is coming ⭐
Boom ! 💥💥
Why use VAR at 3:43?
Wow, hadn't noticed the Time Intel...
Still in Beta, but it's already working 🙂
If u can create a video on last 8 weeks...last 8 weeks ignoring the current week where we have complete data. Eg for a country we dnt had sales for this year. Then my bar chart should pick up last 8 weeks from last year please note I need to show 8 bars for 8 last weeks
Starts at 2:40
What does "Bananas" mean?
ohKAY
Great video, but you can't pronounce 'hierarchy'!
@@petejones7532 yes you are right but I am sure you did understand him..I listen to a lot of English speakers mispronouncing some Spanish words but despite it I understand them
Disappointed that you didn't show how to do it in Power Query, especially since I don't have Bravo/SQL
WTF - You just quickly show all the possible ways to then spend half of the video trying to sell me stupid Bravo. I like the channel and the quality of your videos, but I feel like you dind't really want to help us here.
Can you help create a different kind of Date table? My source data comes from a SharePoint site where all medical provider credential data is held in 60 different columns. I need to consolidate those columns and create a table that shows three columns:
1- Insurance Provider Name
2-Average days from "Cred Paperwork Completed Date to Submitted Date" Per Insurance Carrier
3-Average days from "Submitted Date to Effective Date" Per Insurance Carrier
The desired output should look like below, however, the data is housed in 62 different date columns as previewed in ex 2
Provider Avg Days Completed to Submitted Avg Days Submitted to Effective
Aetna 36 5
Alliance 92 17
Ambetter 54 29
BCBS 18 4
CIGNA 29 14
ETc...
Ex 2
Aetna | Aetna Submit Date | Aetna Effective Date | Alliance Submit Date | Allience Effective Date| etc.....
I have tried to unpivot the columns in a new Date table and I have tried creating 20 calculated columns to count the days then get the averages but I'm not able to create the desired chart. Thanks for all ideas and as an FYI, not all clinicians get credentialed by every insurance provider so there are a lot of blank date fields. The goal is to help management see (columns 1 - how long our team takes to send paperwork off to insurance companies) and (column 2 - which companies are taking the longest to process our credential paperwork).
Sample Data Here: docs.google.com/spreadsheets/d/1utsy80043RvkXQZ6hARR44E7TLUKGnVy/edit#gid=1505525352