Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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",
,
"Type", "Quarter",
"Type Order",2,
"Visual Date Order",
),
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!
Proud to be a Super User!
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
Hi,
I recommend watching this video from BI Elite about the topic:
https://www.youtube.com/watch?v=hilfglpKNRQ
Proud to be a Super User!
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
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",
,
"Type", "Quarter",
"Type Order",2,
"Visual Date Order",
),
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!
Proud to be a Super User!
Great thank-you @ValtteriN
Do you think the week would also work with week commencing instead of week number ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |