Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ?
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |