Date Range Slicer and column & linecharts

[Anonymous]

Hi

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

 

[Rupak_bi]

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.

[Anonymous]

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

[Fowmy · Accepted solution]

@Anonymous 

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

[Anonymous]

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

[Fowmy]

@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")
)

[Anonymous]

Hi,

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