Need to create a month and week slicer but have multiple date fields in table

[vichika]

Need to create a month slicer but have multiple date fields in table in the following format. 

Please help how to create a month slicer

Format

[v-juanli-msft · Accepted solution]

Hi @vichika 

Create calculated columns in your table using DAX, 

Get year->Year function,  month-> month function, week->weeknum function, date->[datetime column].[date]

Then create  a date table using DAX, reference below:

https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

Create relationships between your table and date table based on "date" column(instead of "datetime" column).

Finally, you could add "year", "month","week" columns from "date table" into slicers.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You can create a common calendar table. And use that to join with all dates. One active other inactive.

In calendar use format function to create month week etc. Other function are Weekday, Weeknum

month - Year = format(date[date],"MMM-YYYY")

Also refer :https://community.powerbi.com/t5/Desktop/Need-help-creating-week-number/td-p/14204

How to join with multiple dates, refer: HR-Analytics-Active-Employee-Hire-and-Termination-trend

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[MartynRamsden]

Hi @vichika 

Do you have a date table in your data model with relationships to your fact table?

If so, add a simple calculated column and use that as your slicer.

Month Name = FORMAT( 'Date'[Date], "mmmm" )

If not, you should seriously consider adding one as it makes calculations and filtering a whole load easier.

Here's a good article for reference: www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.