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.
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.
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.
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.
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.
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! :)
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.
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.
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
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?
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.
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.
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!
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?
Thanks for the video. I am working on a project where the fact table already has dates in. would it still be wise to create a date table/calendar? I mean after watching this video I don't think so, but it seems to be suggested by everyone.
Hi I am using the inbuilt date function in PBI. I want to add full year forecast in my table. My columns in my table are actual, budget and forecast. My slicer is the date function. I want to use the slicer so when I select to June it show me be Jan to June actual plus remaining forecast July to December. Would it be possible if I am not use a calendar? Love the channel.
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?
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
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
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
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.
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?
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 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
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
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
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.
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
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
@@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
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?
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 😊😊😊😊
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?
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:)
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 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
Sorry Will Smith, I don't agree. It works for basic visuals and calculations. But eventually you'll run into some limitation with the auto generated date table. Like how each value has its own table, how it doesn't include weeks or whatever other column you need or even that some Dax code doesn't work with the auto table. When you run into this limitation and have spend time trying to work around it then you have to give up and create a date table anyways. And then you have to go through your entire report and apply it. In the end it's much quicker to just have your custom date table from the get-go. Especially since you only need to make it once and can just copy/ paste it into any new report.
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).
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)
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.
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.
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
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.
One question I'd like to understand is the relevance of Calendar[Date] vs Calendar[Date].[Date] which pops up when creating different formulas.
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!
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.
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!
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.
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!!!
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.
You are a hero that is exactly what I needed to know 👍
Patrick, thank you. You make things so simple....
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.
Great tip! I didn't know about this in quick measures. Thanks!
I had no idea. This is great!
Didn't know, will use for sure.
Keep up the great work please 👍
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?
I like the calendar table
Awesome! We know its basics, but never forget ur basics, nevah!
Pat this is a great video... Keep going
This is great, thank you very much. I've been looking for something like this for my reports!!!
If I could like the video multiple times I would. Great stuff!
I like your style. Very easy to understand. Thank you ^^
Great stuff, thanks for the good work you're doing 🙏
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
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.
Awesome. learned something today. Thanks, Patrick!!
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!!!
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!
Thank you!!! I will you this for 💯%!!!!
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
Exactly what I was looking for 👍
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
Awsome really awsome.. Cheers Patrick 😁
Great tip! Thx a lot.
Thank you once again!
شكراً جزيلاً على تعاونكم
Awesome Patrick!
Appreciate that Wyatt! 👊
Awesome Pat!🙏
I'm SOLD and subscribed, tx for much
Thank you Sir!
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?
Thanks for the video.
I am working on a project where the fact table already has dates in. would it still be wise to create a date table/calendar? I mean after watching this video I don't think so, but it seems to be suggested by everyone.
Thanks for the info!!
Most welcome Kendall! 👊
Hi I am using the inbuilt date function in PBI. I want to add full year forecast in my table. My columns in my table are actual, budget and forecast. My slicer is the date function. I want to use the slicer so when I select to June it show me be Jan to June actual plus remaining forecast July to December. Would it be possible if I am not use a calendar? Love the channel.
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!
Great stuff as always - thanks for the tip!
Patrick, thank you for this, very insightful. Can i ask if this translates to a live connection to a SSAS tabular source.
Patrick, as the quick measure stuff, your video is NUUUUUUTS haha
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! 😊👍
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?
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
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
Does Power BI is good for monitoring automations performance in time series analysis, on live or previous data?
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.
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.
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?
This is bananas 🤯!!!
Great video!
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
Nice.
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
Awesome!!!
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
Didn't know you know Patrick is just 👍
Did you just fart @1:55? :P. Kidding aside, I am learning a lot from you. Thank you much! :)
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.
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
Thx Patrick any tip to handle WtD in a nice way?
I am struggling big time, I need to figure out a week over week and just don’t get it . Help!!
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
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
I have 2 sheets, both have dates columns, but I am unable to filter the data correctly on date selection?
Patrick, could you tell me How I can create a folder into a table? please
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
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
Is this works if we want to calculate fiscal YoY, MoM etc measures? Thanks Patrick!
Michael Remoundos Nope
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?
❤️ love that
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:)
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
Hiii
Do you know any websites to practice DAX
Like Hacker rank for SQL
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?
Sorry Will Smith, I don't agree. It works for basic visuals and calculations. But eventually you'll run into some limitation with the auto generated date table. Like how each value has its own table, how it doesn't include weeks or whatever other column you need or even that some Dax code doesn't work with the auto table. When you run into this limitation and have spend time trying to work around it then you have to give up and create a date table anyways. And then you have to go through your entire report and apply it. In the end it's much quicker to just have your custom date table from the get-go. Especially since you only need to make it once and can just copy/ paste it into any new report.
But what if I want a month on month figure change not a percentage change?
Can I make Date hierarchy into Direct Query
I think using quick measures with auto date/time should be filed under. "Just because you can, doesn't mean you should"
Handy... ❤
I love the t-shirt 👍
Thanks. My appreciated.
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).
This is still about date intelligence and NOT time intelligence (hh:mm:ss).
Is there Tableau style automatic time intelligence in Power BI
Probably not that great considering performance not?
I'm getting blank value..can you help me
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?