Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create custom date slicer based current selected periosd

Hi all,

I wish to create a model with two date slicers (current and previous). Ideally, I want when I select on a date slicer between e.g 1/05/2022 to 31/05/2022 the previous date should dynamically move one month before. In other pages, I would like the previous date to be within the same period selected. For example, if i select 2 months on current date slicer, pevious should be automatically show 2 months before the end date of the current. 

I have all the measures such as previous two months sales but all i need is to show the dynamic interactivity between previous and current date slicers. I've tried edit interactions but it seems not to work for my scenario. I'm using two date sliders with between.

Please help 

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , try one of three options

     

    //previous period month
    Same Date range last period =
    var _diff = datediff(MIN('Date'[date]),max('Date'[date]),Month)+1
    var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Month))-1
    var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Month))-1
    Return
    CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
    )


    Same Date range last period Month =
    var _diff = datediff(MIN('Date'[date]),max('Date'[date]),Month)+1
    Return
    CALCULATE(sum(Sales[Sales Amount]),dateadd('Date'[date],-1*_diff,Month))

     

     

    Same Date range last period =
    var _diff = datediff(MIN('Date'[date]),max('Date'[date]),Month)+1
    var _p_st_date = Eomonth(MINX('Date','Date'[date], _1* _diff)-1
    var _p_end_date = Eomonth(MAXX('Date','Date'[date], _1* _diff)
    Return
    CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
    )