Date Range slicer for 3M, 6M, 1M, 3Y, 5Y, 10Y and Change from first value to last value

[phkumar]

Hi Gurus,

I am trying to create a calendar and date slicers on top of a line chart.  I am able to see the line chart updated when I change the calendar but What to be done inorder to achieve the below slicer selection

3M = (EOMONTH(TODAY(),-3)+1)  to  (EOMONTH(TODAY(),-1)+1 )  - This is the date range for 3M (ex: 7/1/24 to 9/1/24)

 similarly for 6M, 1Y, 3Y and so on and forth. 

When I click on 3M, the line chart should reflect the 3 months data and also I need a measure to calculate the % change based on values of selection (i.e. for 3M it should be (Sep - Jul)/Jul}).

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from Ritaf1983 and AsNa_92.

Hi @phkumar ,

I'm sorry I can't open your file due to security reasons, I tried to create a simple example data:

Here is my steps:

1.Create a measure:

Measure = 
VAR _aaa=SELECTEDVALUE('Table'[Column1])
VAR _date=SELECTEDVALUE(Sheet1[Date])
VAR _switch=SWITCH(TRUE(),
_aaa="1Y",_date>=EOMONTH(TODAY(),-12)+1&&_date<=EOMONTH(TODAY(),-1)+1,
_aaa="6M",_date>=EOMONTH(TODAY(),-6)+1&&_date<=EOMONTH(TODAY(),-1)+1,
_aaa="3M",_date>=EOMONTH(TODAY(),-3)+1&&_date<=EOMONTH(TODAY(),-1)+1,
_aaa="F-3M",_date>=EOMONTH(TODAY(),0)+1&&_date<=EOMONTH(TODAY(),2)+1,
_aaa="F-6M",_date>=EOMONTH(TODAY(),0)+1&&_date<=EOMONTH(TODAY(),5)+1,
_aaa="F-1Y",_date>=EOMONTH(TODAY(),0)+1&&_date<=EOMONTH(TODAY(),11)+1
)
RETURN
IF(_switch=TRUE(),1,0)

2.Use the measure to filter on the line chart:

3.Create another measure to calculate the percentage:

Measure2 = 
VAR _select=SELECTEDVALUE('Table'[Column1])
VAR _startdate=
    SWITCH(
    _select,
    "3M",EOMONTH(TODAY(),-3)+1,
    "6M",EOMONTH(TODAY(),-6)+1,
    "1Y",EOMONTH(TODAY(),-12)+1,
    "F-3M",EOMONTH(TODAY(),0)+1,
    "F-6M",EOMONTH(TODAY(),0)+1,
    "F-1Y",EOMONTH(TODAY(),0)+1
)
VAR _enddate=
SWITCH(
    _select,
    "3M",EOMONTH(TODAY(),-1)+1,
    "6M",EOMONTH(TODAY(),5)+1,
    "1Y",EOMONTH(TODAY(),-1)+1,
    "F-3M",EOMONTH(TODAY(),2)+1,
    "F-6M",EOMONTH(TODAY(),5)+1,
    "F-1Y",EOMONTH(TODAY(),11)+1
)
VAR _firstvalue=CALCULATE(SUM('Sheet1'[Value]),'Sheet1'[Date]<=_startdate)
VAR _lastvalue=CALCULATE(SUM('Sheet1'[Value]),'Sheet1'[Date]<=_enddate)
RETURN
DIVIDE(_lastvalue-_firstvalue,_firstvalue)

4.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[phkumar]

Thank you so much, Linhui 🙂. It did solve majority of the issue I was facing. However, I'd appreciate a lot if you could help me achieve the below.

1. How can I make both slicer and date picker work in conjunction? (currently, the slicer doesn't work accurately if i am using the calendar and vice-versa)

Example: I have selected dates between Feb 2024 to Aug 2024. After this, I've selected 1Y, but still the chart doesn't reflect dates back to 1 year. It starts from Feb 2024 instead of Sep2023-Aug2024. Is there a fix for this?

Thank you

[v-linhuizh-msft]

Hi @phkumar ,

In that case, the two slicers are filtering the dates on the x-axis together, so what you get is the intersection of the two slicers. If I understand correctly, what you should need is the union, then you can create a date table again as a field for the date slicer:

Then modify the original formula, Using || in an if statement gives us the union:

Measure = 
VAR _aaa=SELECTEDVALUE('Table'[Column1])
VAR _date=SELECTEDVALUE(Sheet1[Date])
VAR _switch=SWITCH(TRUE(),
_aaa="1Y",_date>=EOMONTH(TODAY(),-12)+1&&_date<=EOMONTH(TODAY(),-1)+1,
_aaa="6M",_date>=EOMONTH(TODAY(),-6)+1&&_date<=EOMONTH(TODAY(),-1)+1,
_aaa="3M",_date>=EOMONTH(TODAY(),-3)+1&&_date<=EOMONTH(TODAY(),-1)+1,
_aaa="F-3M",_date>=EOMONTH(TODAY(),0)+1&&_date<=EOMONTH(TODAY(),2)+1,
_aaa="F-6M",_date>=EOMONTH(TODAY(),0)+1&&_date<=EOMONTH(TODAY(),5)+1,
_aaa="F-1Y",_date>=EOMONTH(TODAY(),0)+1&&_date<=EOMONTH(TODAY(),11)+1
)
RETURN
IF(_switch=TRUE()||(_date>=MIN('Date'[Date])&&_date<=MAX('Date'[Date])),1,0)

Measure2 = 
VAR _select=SELECTEDVALUE('Table'[Column1])
VAR _startdate=
    SWITCH(
    _select,
    "3M",EOMONTH(TODAY(),-3)+1,
    "6M",EOMONTH(TODAY(),-6)+1,
    "1Y",EOMONTH(TODAY(),-12)+1,
    "F-3M",EOMONTH(TODAY(),0)+1,
    "F-6M",EOMONTH(TODAY(),0)+1,
    "F-1Y",EOMONTH(TODAY(),0)+1
)
VAR _enddate=
SWITCH(
    _select,
    "3M",EOMONTH(TODAY(),-1)+1,
    "6M",EOMONTH(TODAY(),5)+1,
    "1Y",EOMONTH(TODAY(),-1)+1,
    "F-3M",EOMONTH(TODAY(),2)+1,
    "F-6M",EOMONTH(TODAY(),5)+1,
    "F-1Y",EOMONTH(TODAY(),11)+1
)
VAR _finalstartdate=IF(MIN('Date'[Date])>=_startdate,_startdate,MIN('Date'[Date]))
VAR _finalenddate=IF(MAX('Date'[Date])>=_enddate,MAX('Date'[Date]),_enddate)
VAR _firstvalue=CALCULATE(SUM('Sheet1'[Value]),'Sheet1'[Date]<=_finalstartdate)
VAR _lastvalue=CALCULATE(SUM('Sheet1'[Value]),'Sheet1'[Date]<=_finalenddate)
RETURN
DIVIDE(_lastvalue-_firstvalue,_firstvalue)

Then the final result is as follows, based on the dates in the image you can see that the two slicers are "working in conjunction":

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[phkumar]

I appreciate this. Thanks a ton for the idea. Unfortunately, this isn't working for some reason. I am assuming this is a limitation with the logic chosen. 
Ex: 1Y has a calculation defined already and ideally should show the chart range as per that calc. (ex: 1Y should give chart range of Oct 2023 - Sep 2024) and so on and so forth. The other selections were filtering the wrong ranges too unfortunately. 

By the way, I've used a workaround for the earlier technique by creating a bookmark to reset the charts whenver I'd like to switch from Calendar to Slicer. 

[AsNa_92]

Hi,

create new column and assign it to the slicer as below:

Period =

VAR CurrentDate = MAX('DateTable'[Date])

RETURN

SWITCH(

    TRUE(),

    'DateTable'[Date] >= EDATE(CurrentDate, -1), "1M",

    'DateTable'[Date] >= EDATE(CurrentDate, -3), "3M",

    'DateTable'[Date] >= EDATE(CurrentDate, -6), "6M",

    'DateTable'[Date] >= EDATE(CurrentDate, -13),"1Y",

    'DateTable'[Date] >= EDATE(CurrentDate, -36), "3Y",

    'DateTable'[Date] >= EDATE(CurrentDate, -60), "5Y",

    'DateTable'[Date] >= EDATE(CurrentDate, -120), "10Y"

)

[Ritaf1983]

Hi @phkumar 
Please refer to the linked video:
https://www.youtube.com/watch?v=Su1mERlwNdM

If it doesn't help 

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[phkumar]

Hi @Ritaf1983 

I saw the video earlier but couldn't follow it . It looks like the slicer or calendar selection, only one of them can be used. I've attached the pbix below if you could help!

Dash 

Thanks a ton!