Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Custom date range in Line chart from slicer selection

Hi All,   I would like some help on this one as I cannot figure it out.   I have a regular date range slicer for Date.    I want to show the last 3 month from the max selected date in the slice...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a date table and a measure to meet your requirement.

    The date table has no relationship with sales table.

     

    1. Create a date table to be a slicer.

     

    Date = CALENDAR("2020/1/1","2020/8/1")

     

     

     

    2. Then we can create a measure.

     

    Sales Last 60 d = 
var _SelectDate = SELECTEDVALUE('Date'[Date])
var _Fdate = _SelectDate-60
var _result = 
calculate(
    SUM('Sales table'[Sales]), 
    FILTER (
        'Sales table',
        'Sales table'[Date]<=_SelectDate && 'Sales table'[Date] >=_Fdate))
return
IF(
    ISBLANK(_SelectDate),SUM('Sales table'[Sales]),_result)

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can create a date table and a measure to meet your requirement.

The date table has no relationship with sales table.

 

1. Create a date table to be a slicer.

 

Date = CALENDAR("2020/1/1","2020/8/1")

 

 

 

2. Then we can create a measure.

 

Sales Last 60 d = 
var _SelectDate = SELECTEDVALUE('Date'[Date])
var _Fdate = _SelectDate-60
var _result = 
calculate(
    SUM('Sales table'[Sales]), 
    FILTER (
        'Sales table',
        'Sales table'[Date]<=_SelectDate && 'Sales table'[Date] >=_Fdate))
return
IF(
    ISBLANK(_SelectDate),SUM('Sales table'[Sales]),_result)

 

 

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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