Power BI - Dynamic Date Axis Granularity (Drilldown Alternative)

Рет қаралды 29,945

Күн бұрын

In this Power BI tutorial, learn how to set up a visual that can show dynamic date granularities via slicer selection! Your user will be able to choose to show the visual on a daily, monthly, or yearly level. They can also select the "Dynamic" option which will select the proper granularity based on the selected date range!
To enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Blog Post/PBIX Download:
bielite.com/blog/dynamic-date...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite
0:00 Demo of finished product
1:48 Setting up Daily, Monthly, and Yearly Selections
7:10 Adding "Dynamic" selection
11:48 Outro

Пікірлер: 69

@mcnater 3 жыл бұрын

Always a good day when another BI Elite vid drops! This a great use case where "bi-directional" isn't a bad thing. Love it.

@BIElite 3 жыл бұрын

Haha I was thinking the same thing!

@satishkumardommeti8282 3 жыл бұрын

This was exactly what I was looking for. Great, simple and elegant solution.

@aliseamoyseenkoisaksson6775 3 жыл бұрын

Thank you for the great video and very pedagogical approach! Tha's exactly the user case I need to fix today. Going to share this to my colleagues.

@hadriennouschi8561 Жыл бұрын

Hello all! To add the weekly aggregation: ADDCOLUMNS( CALENDAR(MIN('Data'[Date]),MAX('Data'[Date])),"Visual Date", [Date] - WEEKDAY([Date],2)+1,"Type", "Weekly", "Order",2),

@carloxfcddsm183 Жыл бұрын

Thanks a lot do you have the fomula for the quarterly aggregation?

@erikdessiex1026 3 жыл бұрын

The way you implemented the "visual date" column is really clever ! Very interesting video. Thanks !

@BIElite 3 жыл бұрын

Thanks Erik!

@meirco16 Жыл бұрын

Thank you for a great lesson

@hagargreif Жыл бұрын

Wonderful!! Many thanks

@thereal_jasonleong 3 жыл бұрын

coming from reddit, great video, subscribed!

@BIElite 3 жыл бұрын

Awesome Jason, welcome!

@julioandrezago1801 3 жыл бұрын

I added a little tweak to the solution, for date formatting: -- Changing visual date formate based on granularity: ADDCOLUMNS ( CALENDAR ( MIN ( 'Calendar'[Date] ), ( MAX ( 'Calendar'[Date] ) ) ), "Visual Date", FORMAT ( [Date], "mmm" ) & "/" & FORMAT ( [Date], "yy" ), "Order By", FORMAT([Date],"yyyy") & "/" & FORMAT([Date],"MM"), "Type", "Mensal", "Order", 2 ), ADDCOLUMNS ( CALENDAR ( MIN ( 'Calendar'[Date] ), ( MAX ( 'Calendar'[Date] ) ) ), "Visual Date", FORMAT([Date],"yyyy"), "Order By", FORMAT([Date],"yyyy"), "Type", "Anual", "Order", 3 And don't forget to order the Visual Date column by the "Order By" column so you get correct data on your visuals. Hope this helps!

@terence3787 Жыл бұрын

If the Value Date are formatted like this, the chart's X-axis will become categorical. And if the chart is too narrow, the dreaded scrollbar will show up. Any ways to fix this?

@InfernoHellfire30 3 жыл бұрын

Brilliant!

@BIElite 3 жыл бұрын

Thank you Evgeniy!

@leena3483 3 жыл бұрын

Great video 👍🏻

@BIElite 3 жыл бұрын

Thank you Aleena!

@myong4317 2 жыл бұрын

When the slicer is selected on "Yearly", I got the date axis with the label "Jan 2011", "Jun 2011", "Jan 2012", "Jun 2012" instead of just "2011" and "2012". Is there any way that I can solve this?

@terence3787 Жыл бұрын

I am having the same issue. Any fixes discovered?

@georgefrewin2343 2 жыл бұрын

Hey, thanks for the vid. When I set up the yearly grain (at 7:13) i get all the month of 2019 but all the sales values showing in Jan. the rest of the months are there but with 0 values. Any ideas where i'm going wrong?

@user-qh9df1bv2n 2 ай бұрын

Amazing!!!!!!!!

@NotiaRS 3 жыл бұрын

Pretty cool solution

@BIElite 3 жыл бұрын

Thank you!

@vikrantnag86 2 жыл бұрын

Thanks for the videos. It we need to add week then how should the measure be written

@oliver3787 2 жыл бұрын

Hi, grate video but I am having trouble with the sorting in the graph any suggestion. I cant find a solution for weekly and monthly dates

@tylerpippin7168 Жыл бұрын

How would you add Time-Comparison Intelligence to these visuals? For example I have a bar-line chart where bar shows current sales and line shows last years sales. Bars will show and adjust properly but last years sales do not show at all. Thoughts?

@paulabatistadesouza 3 жыл бұрын

Great solution! Drill down is really not so friendly for new users, so it's really very useful to have the filter option. I did not understand the TopN filter though. When your Slicer Selection is "monthly", the respective "monthly" Dynamic Date type turns "1". How do it ensure no other type will have 1 assigned as well, so the TopN will always work? Because if it is true for more than one type, and the last day is the first day of the month or year, wouldn't it be a risk of giving the wrong visualization?

@sawankumar2088 3 жыл бұрын

Park I want to show sum of time taken in different project task which I'm showing in other table visual with different dataset , how can I do this?

@spandanagudiseva5273 3 жыл бұрын

How do I include quarter here

@radekou 3 жыл бұрын

Neat trick

@BIElite 3 жыл бұрын

Thanks!

@dennismul4523 2 жыл бұрын

Nice work. Could you include week as an extra level in this model? And would it work without scroll bars? That would be amazing.

@hadriennouschi8561 Жыл бұрын

ADDCOLUMNS( CALENDAR(MIN('Data'[Date]),MAX('Data'[Date])),"Visual Date", [Date] - WEEKDAY([Date],2)+1,"Type", "Weekly", "Order",2),

@AMITKUMARTRIPATHYtheboss 3 жыл бұрын

Really Cool trick , I just want to understand if you have any vedio handling relationships between multiple tables inside a model

@BIElite 3 жыл бұрын

Hi Amit, I don't think I have any videos on this directly. I would recommend SQLBI for their content on relationships.

@AMITKUMARTRIPATHYtheboss 3 жыл бұрын

@@BIElite Thanks

@cvauvert Жыл бұрын

Thank you for this! I have a problem with the Visual Date format, though. It is in Text and when I try to change to date format, it "complains" and the whole thing goes "Try to fix". The Date column is correct with dd/mm/yyyy, but the Visual date is mm/dd/yyyy and not really a date format. The result is that it sorts the axis wrong.. Help 🙂

@sadinenim5360 2 жыл бұрын

How will I do the same thing in excel data model. it was returning the dates instead of years and months.

@yousefalq61 2 жыл бұрын

What if i was to do quarterly?

@klgyal2010 2 жыл бұрын

How would I do this for weekly please?

@catnapwat 3 жыл бұрын

I got to about 9:05 and my head exploded

@BIElite 3 жыл бұрын

Lol, I spent hours on this solution so I know what you mean

@zxccxz164 2 жыл бұрын

nice but, how can i do this so you can switch between say ORDER DATE / SHIP DATE

@brianwu9328 2 жыл бұрын

Mind blowning. But its not working with DATEADD function which cant do yoy comparison. Hope can figure out how to fix this.

@nick-youraverageamericanda7813 Жыл бұрын

I set this up, but on the yearly button, it's pulling in the months still and showing as blank, with the yearly totals showing in Jan of each year. Any idea why that could be happening? Best I can tell difference wise, is my dates in the tables are hierarchy based and I don't yours are in this video.

@juniorexperience Жыл бұрын

I had the same issue. Here is the steps it tooks to get round: ADDCOLUMNS( CALENDAR(MIN(Data[Data]), MAX(Data[Data])), "Visual Date", FORMAT([Date], "yyyy"), "Type", "Yearly", "Order", 3 )

@Thursdaysaretheworst 3 жыл бұрын

I am stuck on how to include quarter into the Visual Date Column. Any help would be appreciated.

@PratikshaDhanawade Жыл бұрын

@ManBoy89 have you found a solution on this?

@carloxfcddsm183 Жыл бұрын

hello, anyone know how to get the Quarter Granularity?

@PratikshaDhanawade Жыл бұрын

Can anyone help on adding a quarter in the type

@mahathmasadineni2884 2 жыл бұрын

I have this doubt how it is months and years we have date only in visual date field can you please tell me.

@nickcleveland2810 3 жыл бұрын

Anyone know what the code would be to add a column for the week to the “Dynamic Date Selection” table? Currently I have this code: =DATE(YEAR([Date]),MONTH([Date]),DAY([Date] - WEEKDAY([Date],1) +1)) For the most part it works. However, when the date column flips to the next month during the middle of the week, the visual date calculating the start of the week messes up. For example, once January 2021 turns to February, it calculates the week start date of 2/1/2021 as 3/3/21. This is throwing me for a loop and I can’t wrap my head around why it’s doing that. Any suggestions would be welcome!

@jorwaar9039 3 жыл бұрын

Looking for this as well. Did you find the solution?

@nickcleveland2810 3 жыл бұрын

Yes! All that’s needed is this: = [Date] - WEEKDAY([Date], 1) + 1 Turns out I was needlessly complicating the formula.

@jorwaar9039 3 жыл бұрын

@@nickcleveland2810 Great solution, works like a charm. Thanks!

@manoj280in Жыл бұрын

@@nickcleveland2810 Thanks for this great help

@thierrythomas6640 3 жыл бұрын

Just one word : awesome ! I begin in PowerBI : how many years it needs to do something like that ?

@BIElite 3 жыл бұрын

Thanks Thierry! Follow along with the video instructions and you will be able to implement it today :)

@thierrythomas6640 3 жыл бұрын

@@BIElite I did it and it works well. In fact my question was : how long did you work with Power BI?

@Mucahtkutlu 2 жыл бұрын

On these visuals, this method might fail when you have a "Time Comparison-Intelligence" calculation, e.g., YA (SAMEPERIODLASTYEAR).

@saratchandra7388 2 жыл бұрын

I have date hierarchy sync slicer slicer in report want to show default by 3 months on line chart and if user select on slicer it should change axis we can't use relative date slicer we can't have latest 3 months in date hierarchy slicer as if we select latest 3 months it will filter other hierarchy slicer in report page we used Calculate (countrows( x[column name],filter ( all( x),filter on some columns in x table) ,datebetween ( calendar[date],max( calendar[Date])- 29days ,max( calendar [Date]),Date between ( calendar[Date],max (calendar (dt)],-3,months)) How to change dax to achieve that one we need both 3 months and full months data which sync slicer.Any help on dax would be appreciated?

@yashodasathuluri4098 2 жыл бұрын

How to add weekly

@yashodasathuluri4098 2 жыл бұрын

@@ani-lysis Thank you

@tarunsharma5479 Жыл бұрын

@@yashodasathuluri4098 How can we add weekly?

@casseb3555 Жыл бұрын

How can we add weekly? Please

@hadriennouschi8561 Жыл бұрын

ADDCOLUMNS( CALENDAR(MIN('Data'[Date]),MAX('Data'[Date])),"Visual Date", [Date] - WEEKDAY([Date],2)+1,"Type", "Weekly", "Order",2),

@francol.dejuana7862 10 ай бұрын

I don't really recommend this tutorial. This video makes something as simple as using a slicer with the month / week column, into a large dax typing formula. This ain't helpful for new people. It's easier to just make a slicer with custom columns from the calendar table to switch filters between year / month or even week day (monday, wednesday, etc)