Do you need a date table for time intelligence in Power BI? Nope! (Quick Measures)
Вставка
- Опубліковано 21 лип 2024
- Everyone tells you that you need a date table for time intelligence in Power BI. Not so fast! Patrick shows you how you can use quick measures, like year over year, with a quick measure and no date table!
Download Sample: guyinacu.be/quickmeasuresample
📢 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 #TimeIntelligence #GuyInACube - Наука та технологія
Wow! Great Revelation. Never quick measure was loaded with such powerful resource. I will play with this.
Saved me some time on a video I was working on for release on Friday, I can just reference this video now. Keep up the good work!
I know this video is 3 years old, but just want to say this was the perfect video for the answer that I needed to get my vizs to work. Thank you for a straightforward and succinct explanation.
Thanks for giving some color to it! i was getting confused with Dates and time inteligence, now I understand how it works thanks to you!
I keep coming back to this for reference. It has been so useful. THANKS!!!
This is great, thank you very much. I've been looking for something like this for my reports!!!
Great tip! I didn't know about this in quick measures. Thanks!
Very handy and good to know! The company I work for uses a (4-5-4) fiscal calendar. Can you do a video showing similar tips/tricks with fiscal dates/periods? Might be a large audience waiting for some fiscal date/time intelligence.
This is great, thanks! Wish I had watched this before I spent all day trying to figure out how to build a date table and integrate into my model.
You really, really helped me overcome some issues I was having in a couple of my models. Seems so simple, but everything is simple once you know it.
YES! We have been there so many times. Glad to hear you have made progress 👊
Thank you Patrick. It's a very nice reminding. Sometimes there's a much easier way to fix our problems.
I've been stuck in MTD YTD buttons for days already. I read a lot of articles and watched a lot of video, but just can't fix my problems. After watching your video, I think I can just use the original date slicer, make a bookmark of YTD, and build a button to connect this bookmark. The disadvantage is that I need to modify the bookmark every month. but at least it can help to solve my problem.
Patrick, thank you. You make things so simple....
Didn't know, will use for sure.
Keep up the great work please 👍
Awesome. learned something today. Thanks, Patrick!!
Thank you so much for sharing this wonderful tips or feature of power BI, I normally try to avoid or hate writing the codes manually instead prefer to do the system or application to do the job for me. thank you thank you thank you so so much sir. May god give you more wisdom knowledge and health. Love you from India/ Bharat.
Great stuff, thanks for the good work you're doing 🙏
Awesome! We know its basics, but never forget ur basics, nevah!
One question I'd like to understand is the relevance of Calendar[Date] vs Calendar[Date].[Date] which pops up when creating different formulas.
If I could like the video multiple times I would. Great stuff!
I had no idea. This is great!
I like your style. Very easy to understand. Thank you ^^
Thank you Patrick, love your videos (and that of Adam's!) with just the right things covered succinctly! You guys are doing great job, keep it up! 😊👍
Awsome really awsome.. Cheers Patrick 😁
Thanks for this Patrick, I feel so privileged that I took this feature for granted! I use this feature without even thinking about it, Power BI made it super intuitive to use! :)
Fantastic!
You are a hero that is exactly what I needed to know 👍
I'm SOLD and subscribed, tx for much
Awesome Pat!🙏
Love this! One of my ongoing challenges has been that the executives really want to see YTD, QTD, MTD averages, which do not exist in the quick measures. Not a big deal now, but it was a struggle to figure out.
Do share how to do it!!!
Great tip! Thx a lot.
I now understand why adding the calendar table broke my calcs when I joined it to the model. I wish that was more intuitive to understand but seems an easy enough modification.
Thank you!!! I will you this for 💯%!!!!
Pat this is a great video... Keep going
My 2 cents is that the value of a Calendar table in a data model is not that it's necessary because it has a column of date values to create period-to-date aggregations (like MTD, QTD, YTD). The value of a Calendar table consists of the various attributes associated with each date. Attributes like the name of the day (Monday, Tuesday, Wednesday, etc.), whether a particular date is a Work Day, Week End or Holiday, which day of the year it is (like day 232). Stuff like that. Suppose, for example, you want to calculate a daily average sales value but your business doesn't do sales on week ends or holidays. In this case you'd want a Calendar table with a column indicating whether a date is a business day or not, and then reference that in a DAX measure to filter out those dates from the calculation. Otherwise, the calc would include those non-business days without sales, and your average wouldn't really be meaningful. So, yes, you don't actually need a Calendar table ... but you'll be better off in the long run if you do have one.
How to you filter it? I am stuck at this weekend problem
i dont mean to be off topic but does someone know of a way to get back into an Instagram account?
I stupidly forgot the password. I would love any assistance you can offer me.
Thank you once again!
Exactly what I was looking for 👍
Thank you Sir!
Hey guys pretty good stuff, its always enriching to learn different ways. I had a question i've been struggling a couple of days with. So for example i do not have a specific date "11/20/20" what i do have is Fiscalyear.Quarter.Week = "2021.Q4.WK02" and i wanted to start doing measures such as Y/Y , WeekonWeek. I tried passing some left,4 then substracting to had yeear but is not enough context. Perhaps you already have some documentation or video i can go and review for those non standard calendars.
Appreciate it TY
شكراً جزيلاً على تعاونكم
Patrick, thank you for this, very insightful. Can i ask if this translates to a live connection to a SSAS tabular source.
I like the calendar table
Hi Patrick, thank you so much for the explanation, is it possible to have the source files that you used for each video? Thanks again!
great job
Thanks for the info!!
Most welcome Kendall! 👊
Awesome Patrick!
Appreciate that Wyatt! 👊
Thx Patrick, but we always use separate date dimension tables. Auto date tables in the background can make your dataset size very big if you have have a lot of date colums in the model.
#This
i did it too! created date dim tables instead :D
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?
Most of the times i work with a datawarehouse which allready has a Date dimension where al those day, week,month,quarter, year are allready in it
Thanks Patrick, but I believe this auto time intelligence will not work for such scenario where we want slicers to show all other months for which there is no data associated to the measures or fields on the canvas, isn't it?
Hi Patrick need one help related to power bi. Need to showcase blank table in power bi, data in tabular grid should be blank until we use slicer to populate some in tabular grid in power bi
How do I dort my graph by my fiscal months instead of calendar date? When I try to sort by fiscal I get a circular error
I have a date slicer which should show last 12 months as selected when report opens. Further, user should have flexibility to select any other date range. Please any one answer this question
Great video!
Patrick, as the quick measure stuff, your video is NUUUUUUTS haha
Patrick, could you tell me How I can create a folder into a table? please
I have 2 sheets, both have dates columns, but I am unable to filter the data correctly on date selection?
Hi Patrick, what abount Rolling time ? To use in filters like Rolling_Date >= -7 for the last 7 days at any refresh a may do ? What about Time ? Like 5 minutes increment Time analysis ? 2020-07-20 07:08:22 = 2020-07-20 07:05:00
With a strong SQL background, I have been wondering if I do need a date table in PowerBi for every project. Now I found the answer. Thank you guys! Very helpful!
It seems that this will work as long as we only care about intervals where we have information. If there were no sales in an interval (year, quarter, month, day) we can't see the lack of activity, right?
Does Power BI is good for monitoring automations performance in time series analysis, on live or previous data?
Thx Patrick any tip to handle WtD in a nice way?
I have been doing the same YoY calculation without using a calendar table. In addition, i have a requirement to select the current month by default. I create an additional column in Power Query that indicates current month. When i select this current month filter, the DAX calculation for YoY collapses. Anyone face this issue?
I really appreciate this video, I tried making a date table, and it was screwing up my numbers, (Context Transitioning)
I got so frustrated I basically rage quit Dax (mainly because I think its stupid that your subtotals in a matrix are not guaranteed to match the rows above)
I mean I'm sure there is some design based explanation but what kinda design is that? why on earth would somebody even make that a thing, it makes it so much scarier to trust your work since u cant just quickly check the subtotal against a SQL query
I think i'll give it a second try using these videos, keeping everything in a flat table feels a lot safer
How do I get Year-Month on the axis? I find I can show all across year boundaries, or I can sort months correctly, but not both. It's a pain!
All I want is something like this:
Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021, July 2021, Aug 2021, Sep 2021, Nov 2021, Dec 2021, Jan 2022
Selecting 'Month' from the date hierarchy sends Jan 2022 to the beginning of the list. Doesn't Power BI know that is a more recent January?
i liked this video. Not sure when i'll get to use it. But, i'll definitely give it a test on a dummy data set.
L
Awesome!!!
Hi Patrick, thank you!
Can you please make a video on how to create time segments: QTD, YTD, this Month, Last month, yesterday.
Another thing is a table that compares these dates for example one column will take this month and the other last month with 2 slivers.
Regards
Will definitley consider it.
I am struggling big time, I need to figure out a week over week and just don’t get it . Help!!
But what if I want a month on month figure change not a percentage change?
Hiii
Do you know any websites to practice DAX
Like Hacker rank for SQL
Great stuff as always - thanks for the tip!
This is bananas 🤯!!!
Great videos! I am a newbie in Powerbi and I have a question see if you can help me:D . I pre-load all my tables in a python script every refresh, and want to offload some calculation on-the-fly. So I want to dynamically subset a table (create a new table) by using a slicer value I selected, but I never succeeded, the following code always generated a blank table for me: new_table=
VAR SELECTED = SELECTEDVALUE('table1'[column])
RETURN
CALCULATETABLE('table2', 'table'[column] = SELECTED). Any idea? I hope it's okay to ask here:)
I'm getting blank value..can you help me
I did something similar. However I would like to point out that this method dosen't work when you have more than one table you want to filter your time intelligence calculations with
Nice.
Hi Patrick, thanks for sharing this video. What I miss is the week (starting on Mondays) dimension in the standard Intelligence. Do you have a solution for this?
Weekly Time Intelligence, will do me alot of good
Didn't know you know Patrick is just 👍
Can I make Date hierarchy into Direct Query
Awesome, I thought it was only for flat single table schema, nice hack to build it off single table then migrate to star. MS should allow the wizard to do the same without throwing an error.
I tried to download the sample, but it gives me an error "Account is cancelled and can not accept new subscribers.
"
❤️ love that
Hey Patrick, thanks for the video. What would happen if you don't have contiguous dates in the OrderDate column? I believe that Time intelligence functions will not work properly.
Actually, auto-time intelligence fills in the gaps. As a result, everything should work perfectly.
This isn't working for me. I've got a date/time field and loading a Fabric semantic model and the dates are just ignored and not auto hierarchised. The column tools shows that the date columns are date/time
Hi Patrick the question is : Did you really avoid the use of a Date Table ? " because what I learned is that using the "auto date/time" feature create a "hidden" date table for each date field in the model
It creates a hidden date table of every data column. So you are correct, it is an easy to use but possible very inefficient way to use a datetable.
Is this works if we want to calculate fiscal YoY, MoM etc measures? Thanks Patrick!
Michael Remoundos Nope
Hi
If I add a column for a semester, so my user selects the semester my measures don't work. Do you know what can I do? It works for quarter, month etc but no with the semester column that I created
Need more context. Where are you adding the column? Is it an actual date table?
Patrick, I've learned from you and the SQLBI guys to disable Auto Time Intelligence because of the performance. I'm confused now :)!
Ignore this video. I'm surprised he has uploaded it. This only works if you have consistent dates in your column.
If his data model had a month where no sales were made then this would fall flat.
This video is all about self-service and quick data analysis. If you are building a true data model as a best practice always use a date table and disable auto-time intelligence.
@@GuyInACube Hey there! thanks for taking the time to clarify. I think the tension field between 'bold exploratory analysis' and 'robust enterprise reporting' is a very compelling topic that is, to a certain degree, reflected here. Would be great to learn more about that. All the best!
Hi Patrick,hope you doing well...
I have stucked at mid of my project that's how to use SQL Store Procedure in Direct Query or Live Connection mode...
can u pls help me on this or give any idea for how to use Store Procedure's in Live connections.
thanks in advance 😊😊😊😊
It is not supported by default. If you do an internet search you will see some work arounds, but it will depends on the source system.
@@GuyInACube thanks for your advise
Great Patrick, thank you for the information. I am doing the same thing. Can you make these two changes. 1. Sort the months on bar chart from Oct to Sep(My FY). 2. Make the months label a short name eg Oct for October. Eagerly waiting..
Ahhh, now I understand. Thanks for joining the LiveStream.
You would need to do two things: Add a Fiscal Month Calculated Column. Something like this:
Fiscal Month Abbrev = FORMAT(pbi_InternetSales[OrderDate], "mmm"). Then create a Fiscal Month Sort Column. Something like this:
Fiscal Month Sort =
var _month = month(pbi_InternetSales[OrderDate])
var _result = IF(_month > 9, _month-9, _month+3)
return
_result
Then sort Fiscal Month by Fiscal Month Sort and that should work. I just recorded a video that will be out soon.
@@pleblanc1972 Thank you Patrick, I will try this one.
@@pleblanc1972 You suggestion sorts months perfectly, But the primary concern remains the same. MoM Variance still shows 0s. I want Sort and Variance at the same time. Can you please try.
I haven't done extensive testing of this DAX, but you can try something like this:
MoM% =
var currentFM =SELECTEDVALUE(pbi_InternetSales[Fiscal Month Sort])
var preFM = if(currentFM = 1, 12,currentFM-1)
var currentMonthTax = CALCULATE(SUM(pbi_InternetSales[TaxAmt]))
var prevMonthTax =
CALCULATE(SUM(pbi_InternetSales[TaxAmt]),
REMOVEFILTERS(pbi_InternetSales),pbi_InternetSales[Fiscal Month Sort] = preFM)
var result = DIVIDE(currentMonthTax, prevMonthTax, 0)
return
result
@@pleblanc1972 Thank you Patrick, with a little variation Variance calculation is fine. But I am not able use Slicer with Month Abberv as it is Text value, tried with Fiscal Month Sort Column(1,2,3....12) That works.
Any solution to have a slicer for Month which can filter my matrix
Thank You
Since there is no YTD-over-YTD change quick measure, how do I write that DAX? I tried to adapt this using _PREV_TOTALYTD, but for some reason it just isn't working. Any help anyone could provide would be awesome!
Rewards Gallons YoY% =
IF(
ISFILTERED('Calendar'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
SUM('Rewards'[Rewards Gallons]),
DATEADD('Calendar'[Date].[Date], -1, YEAR) TOTALYTD(SUM('Rewards'[Rewards Gallons]), 'Calendar'[Date].[Date])
)
RETURN
DIVIDE(SUM('Rewards'[Rewards Gallons]) - __PREV_YEAR, __PREV_YEAR)
One approach may be to get the two different YTD values into variables and then compare those?
I love the t-shirt 👍
Thanks. My appreciated.
Handy... ❤
Is there any easier way to work with fiscal years than to build a calendar table?
Same question I have. Is there a way to set Fiscal period in pbi's time intelligence?
@@raajbir123 Nope. Power BI Auto Date/Time is what it is: year, quarter, month and day...It can't be expanded nor modified, that's the reason why in 99.9% cases you would want to use separate Calendar table
Did you just fart @1:55? :P. Kidding aside, I am learning a lot from you. Thank you much! :)
Yea Its bananas
This is still about date intelligence and NOT time intelligence (hh:mm:ss).
Is there Tableau style automatic time intelligence in Power BI
Anybody else notice the noise at 1:57?
I think using quick measures with auto date/time should be filed under. "Just because you can, doesn't mean you should"
One way or another you have a date table. Either you make it, or Power BI does, but you have one.
The title is somewhat misleading... You DO need a date table, the point is that with the time intelligence option Power BI builds it for you (as you say in the video).