topic Re: Dynamic Date Slicer including a Week and Quarter option in DAX Commands and Tips

Dynamic Date Slicer including a Week and Quarter option

JRA21_13_19_25 — Sun, 16 Jan 2022 20:25:20 GMT

Hello,

I want to create a dynamic date slicer in power BI that would allow the x axis of my bar chart to dynamically change so my end-users can have the option to breakdown the figures Yearly, Quarterly, Monthly, Weekly or Daily.

I have managed to create a slicer for Daily, Monthly and Yearly using the DAX formula below but I’m struggling with adding the Weekly and Quarterly DAX.

To create the dynamic slicer, I have created a table using the following DAX formula:

Dynamic Date Selection =

UNION(

ADDCOLUMNS(

CALENDAR(MIN('Date Table'[Date]),MAX('Date Table'[Date])),

"Visual Date",FORMAT([Date],"dd-mmm-yyyy"),

"Type", "Daily",

"Type Order",1,

"Visual Date Order", FORMAT([Date],"yyyy-mm-dd")

ADDCOLUMNS(

CALENDAR(MIN('Date Table'[Date]),MAX('Date Table'[Date])),

"Visual Date",FORMAT([Date],"MMM yyyy"),

"Type", "Monthly",

"Type Order",2,

"Visual Date Order", FORMAT([Date],"yyyy-mm")

ADDCOLUMNS(

CALENDAR(MIN('Date Table'[Date]),MAX('Date Table'[Date])),

"Visual Date",FORMAT([Date],"yyyy"),

"Type", "Yearly",

"Type Order",3,

"Visual Date Order", FORMAT([Date],"yyyy")

)

Dynamic Date Selection Table:

Below is a pic of what I have created so far:

Please can anyone help me with the Week and Quarter DAX?

Many thanks

Re: Dynamic Date Slicer including a Week and Quarter option

ValtteriN — Sun, 16 Jan 2022 20:56:53 GMT

Hi,

I recommend watching this video from BI Elite about the topic:
https://www.youtube.com/watch?v=hilfglpKNRQ

Re: Dynamic Date Slicer including a Week and Quarter option

JRA21_13_19_25 — Mon, 17 Jan 2022 05:38:57 GMT

Hi ValtteriN ,

Yes thanks this is where I got the DAX for the Day, Month and Year but he doesn't explain how to do it for Week and Quarter. Any ideas how?

Thanks

Re: Dynamic Date Slicer including a Week and Quarter option

ValtteriN — Mon, 17 Jan 2022 06:50:51 GMT

I see,

The basic structure in ADDCOLUMNS for yearly and monthly is to have a date in theire respective formats to have this for quarter and week is similar to having them in calendar.

E.g.

ADDCOLUMNS(

CALENDAR(MIN('Date Table'[Date]),MAX('Date Table'[Date])),

"Visual Date",

CONCATENATE(YEAR([Date])& "/",QUARTER([Date]))

"Type", "Quarter",

"Type Order",2,

"Visual Date Order",

CONCATENATE(YEAR([Date])& "/",QUARTER([Date]))

For week you can use WEEKNUM.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Dynamic Date Slicer including a Week and Quarter option

JRA21_13_19_25 — Tue, 18 Jan 2022 07:50:32 GMT

Great thank-you ValtteriN

Do you think the week would also work with week commencing instead of week number ?

Re: Dynamic Date Slicer including a Week and Quarter option

Anonymous — Thu, 20 Jan 2022 12:01:51 GMT

Hey Hi , Did You Got the Solution For this Query ? Im Also got Strucked with these In Weekly And hourly Basis

If You Know Can you suggest me How can i change dynamically in weekly and Hourly