Power BI - Dynamic Date Axis Granularity (Drilldown Alternative)

[@mcnater]

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]

Haha I was thinking the same thing!

[@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]

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

[@BIElite]

Thanks Erik!

[@myong4317]

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?

[@rodie82]

This is a game changer, thank you! Super impressive on its own, even more so if the video wasn't sped up.

[@spandanagudiseva5273]

How do I include quarter here

[@georgefrewin2343]

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?

[@satishkumardommeti8282]

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

[@thereal_jasonleong]

coming from reddit, great video, subscribed!

[@BIElite]

Awesome Jason, welcome!

[@yousefalq61]

What if i was to do quarterly?

[@vikrantnag86]

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

[@julioandrezago1801]

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?

[@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]

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?

[@PratikshaDhanawade]

Can anyone help on adding a quarter in the type

[@AMITKUMARTRIPATHYtheboss]

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

[@BIElite]

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

[@AMITKUMARTRIPATHYtheboss]

@@BIElite Thanks

[@carloxfcddsm183]

hello, anyone know how to get the Quarter Granularity?

[@catnapwat]

I got to about 9:05 and my head exploded

[@BIElite]

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

[@aliseamoyseenkoisaksson6775]

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.

[@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 🙂

[@zxccxz164]

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

[@mahathmasadineni2884]

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

[@brianwu9328]

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

[@oliver3787]

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?

[@klgyal2010]

How would I do this for weekly please?

[@meirco16]

Thank you for a great lesson

[@paulabatistadesouza]

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]

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?

[@dennismul4523]

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),

[@leena3483]

Great video 👍🏻

[@BIElite]

Thank you Aleena!

[@hagargreif]

Wonderful!! Many thanks

[@yashodasathuluri4098]

How to add weekly

[@yashodasathuluri4098]

@@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),

[@thierrythomas6640]

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

[@BIElite]

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

[@thierrythomas6640]

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

[@NotiaRS]

Pretty cool solution

[@BIElite]

Thank you!

[@nickcleveland2810]

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]

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

[@nickcleveland2810]

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

[@jorwaar9039]

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

[@manoj280in]

@@nickcleveland2810 Thanks for this great help

[@saratchandra7388]

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?

[@InfernoHellfire30]

Brilliant!

[@BIElite]

Thank you Evgeniy!

[@ВікторіяНечипорук-ю8ш]

Thanks a lot)

[@Mucahtkutlu]

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

[@JoãoOtávioRodrigues-f7k]

Amazing!!!!!!!!

[@radekou]

Neat trick

[@BIElite]

Thanks!

[@francol.dejuana7862]

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)

[@sadinenim5360]

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