Dynamic Date Slicer including a Week and Quarter option

[JRA21_13_19_25]

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

[Anonymous]

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

[ValtteriN]

Hi,

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

[JRA21_13_19_25]

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

[ValtteriN · Accepted 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",

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!

[JRA21_13_19_25]

Great thank-you @ValtteriN 

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