topic Date Range Slicer and column & linecharts in DAX Commands and Tips

Date Range Slicer and column & linecharts

Anonymous — Sat, 25 Nov 2023 02:55:13 GMT

Kindly guide me on this

How can I show column & line chart based on a date range filter (Slicer)

Date range format like the below shown image

Re: Date Range Slicer and column & linecharts

Rupak_bi — Sat, 25 Nov 2023 05:28:45 GMT

unable to understand what challeges you are facing. I am sure you put date in X axis and value in Y axis. Now what wrong going on. please elaborate.

Re: Date Range Slicer and column & linecharts

Anonymous — Sat, 25 Nov 2023 05:59:03 GMT

Hi Rupak,

Many thanks for your reply.

Image 1 shows my graph and the date slicer. I want to change my date range slicer simlar to image 2

Image 1

Image 2

Re: Date Range Slicer and column & linecharts

Fowmy — Sat, 25 Nov 2023 06:36:42 GMT

Anonymous

You can use the YearMonth in your slicer and chagnet the style to "Between" under options in the formatting pane:

Re: Date Range Slicer and column & linecharts

Anonymous — Sat, 25 Nov 2023 11:44:46 GMT

Hi,

I tried to add the Year Month Sort, However its not working as expected.

It does not showing the between options

This is my Calender Table

Calendar = ADDCOLUMNS(CALENDAR(DATE(2013,1,1), DATE(YEAR(TODAY())+1,12,31))

, "Year", YEAR([Date])

, "Quarter", QUARTER([Date])

, "Month Num", MONTH([Date])

, "Week Num" , WEEKNUM([Date])

, "Week Day" , WEEKDAY([Date])

, "Day", DAY([Date])

, "Month", FORMAT([Date], "Mmmm")

, "Week", FORMAT([Date] , "dddd")

, "Quarter Number", "Q" & QUARTER([Date])

, "Year Month" , YEAR([Date]) & " " & FORMAT([Date], "Mmmm")

, "Year Month Sort", YEAR([Date]) & MONTH([Date])

, "Year Quarter", YEAR([Date]) & " " & "Q" & QUARTER([Date])

, "Year Quarter Sort", YEAR([Date]) & QUARTER([Date])

, "Fiscal Year", YEAR(EDATE([Date], 11))

, "Fiscal Month", Month(EDATE([Date], 11))

, "Fiscal Quarter", QUARTER(EDATE([Date], 11))

, "Fiscal Week", WEEKNUM(EDATE([Date], 11))

, "Last 6 Months" , IF(EOMONTH([Date], 0) > EOMONTH(TODAY(), -6) && [Date] <= TODAY(), TRUE())

, "Last 6 Months Complete" , IF(EOMONTH(TODAY(), 0) <> TODAY()

, IF(EOMONTH([Date], 0) > EOMONTH(TODAY(), -7) && [Date] <= EOMONTH(TODAY(), -1) , TRUE())

, IF(EOMONTH([Date], 0) > EOMONTH(TODAY(), -6) && [Date] <= TODAY(), TRUE())

))

Kindly advice

Thanks

Re: Date Range Slicer and column & linecharts

Fowmy — Sat, 25 Nov 2023 11:53:01 GMT

Anonymous

Make sure the column is a number. Check out my dates table where the "Year Month No" is a number column which will allow it to be used on a slicer.

Dates = ADDCOLUMNS( CALENDAR("01/01/2020","31/12/2021"), "Month No" , MONTH([Date]), "Month Name", FORMAT([Date],"Mmmm"), "Year",YEAR([Date]), "Year Month No", INT(FORMAT([Date],"yyyymm")), "Year Month" , FORMAT([Date],"Mmm yyyy"), "Quarter No", QUARTER([Date]), "Quarter" , FORMAT([Date],"\QQ") )

Re: Date Range Slicer and column & linecharts

Anonymous — Sat, 25 Nov 2023 12:42:06 GMT

Hi,

Its working now. Thank you so much for the support!