Power bi data range slicer beginning date

[bssienes]

Hi all,

As you can see in the figure below, Last 1 month is selected on the date range slicer. However, it is showing the date range based on today's date. I want this filter to select dates based on the current month so the beginning date on the filter must be the 1st of May. If I select last 2 months, then beginning date should be april 1st. How can I do this?

[Ashish_Mathur]

Hi,

Assuming you have a Calendar Table with Year and Month columns, just drag those 2 columns to your visual and write this measure

Measure = sum(Data[Sales])

[amitchandak]

@bssienes , You have to create a date slicer (independent date table), then a number slicer -numeric parameter ([what if measure]) and then a period slicer (using independent table)

Then you have get dates like

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])

var _min = Switch(selectedvalues(period[period]) ,

"Month", date(year(_max), month(_max)  + [what if patam], day(_max) ) ,

"Day", date(year(_max), month(_max)  , day(_max) + [what if patam]) ,

"Quarter", date(year(_max), month(_max)  + [what if patam]*3, day(_max) ) ,

"Year", date(year(_max) + [what if patam] , month(_max)  , day(_max) )

)
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))