Forum Discussion

kulpowerbi's avatar
kulpowerbi
Helper I
5 years ago
Solved

DateBetween using date slicer

Hi amitchandak , I hope you can help me here.

I am looking for a solution where users can select FromDate that will be crafted usng (year, month,quarter all three using different slicer) and the same is for ToDate using slicer.
FromDate and ToDate both are separate tables and having a date field. Not connected with any tables in the model.

I am using this slicer to generate the date range for DatesBetween Function. but somehow when months are selected. it's not working. for year selection it's working.

Model: 

 

The PowerBi file is in OneDrive attached. Thank you for your help in advance.

Selected_FromTO = 

Var FromMonth = MIN('From'[Date].[MonthNo])

var ToMonth = Max('To'[Date].[MonthNo])

var FromYear = MIN('From'[Date].[Year])

var ToYear = Max('To'[Date].[Year])

var MonthRange= FromMonth&"_"& ToMonth

var YearRange = FromYear&"_"& ToYear

var MonthYearRange = MonthRange &"___"&YearRange

return CALCULATE(SUM(Table1[sales]),

 DATESBETWEEN(Table1[date].[Date],DATE(FromYear,FromMonth,1),DATE(ToYear,ToMonth,1)

 ))
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • kulpowerbi's avatar
    kulpowerbi
    5 years ago

    Hi amitchandak  Thanks for the quick response. The solution is working for Year and Month Slicer but not working on Quarter Selection. Could you please look into this?

     

     

     

    Thanks.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    kulpowerbi , as these are date Min and max

     

    Selected_FromTO =
    Var _Min = MINX(allselected('From'), 'From'[Date])
    Var _Max = MAXX(allselected('To'), 'To'[Date])
    return

    CALCULATE(SUM(Table1[sales]), filter(Table1, Table1[date]>=_min && Table1[date]<=_max))

     

    Also, I would suggest you create column for year, month , day etc in your date tables 

  • kulpowerbi's avatar
    kulpowerbi
    Helper I
    5 years ago

    Hi amitchandak  Thanks for the quick response. The solution is working for Year and Month Slicer but not working on Quarter Selection. Could you please look into this?

     

     

     

    Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    kulpowerbi 
    Did you solved? I have checked the pbix and see no problem with slicers and visuals.

     

    Paul Zheng _ Community Support Team