Custom date filter

[mesuyog]

I have to create custom date filter where Month start should be last months 13 and month end should be that months 12.

For Example -

Normally : January 2022 Starts - 1st ends at 31st

I need : January 2022 Start - 13th Dec 2021 and should end on 12 Jan 2022

Like wise every month should start at 13th of last month and end on 12th of that perticular month. 

[v-eqin-msft · Accepted solution]

Hi @mesuyog ,

What's your expected output? If you want to simply apply a date filter, you may drag Date column to Slicer field, then you could custom date period:

Or if you want to re-arrange the date month, for example, 

 2021 December is  from   2021/12/13  to   2022/1/12  

 2022 January     is  from   2022/1/13    to   2022/2/12 

Please create a column:

New YearMonth = 
var _date=IF(DAY([Date])>=13,[Date],EOMONTH('Table'[Date],-1))
return FORMAT(_date,"yyyy mmmm")

And I created another column for sort:

Output:

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

[Pbiuserr]

You didnt mention it, perhaps you can try pattern down below

Column = SWITCH( TRUE()

, Calendar[Date] >= DATE(2019,1,1) && Calendar[Date] <= DATE(2020,12,31), 1

, Calendar[Date] >= DATE(2021,1,1) && Calendar[Date] <= DATE(2021,12,31), 2

)

Just adjust dates for your requirements

[Pbiuserr]

Try something like this

custom date filter =

VAR start_date = DATE (2021, 12, 13)
VAR end_date = DATE (2022, 01, 12)

RETURN

IF (

'Calendar'[Date] IN DATESBETWEEN('Calendar'[Date], start_date, end_date),

1,

0

)

and you've got a filter for the visuals

[mesuyog]

Thanks but it will not work as I need to create the same for all years. I have data for total 5 years and will be updating moving forwarad.

[Pbiuserr]

You didnt mention you've got multiple years

Try this, just adjust dates up to your requirements ( i dont know your dates )

Column = SWITCH( TRUE()

, Calendar[Date] >= DATE(2019,1,1) && Calendar[Date] <= DATE(2020,12,31), 1

, Calendar[Date] >= DATE(2021,1,1) && Calendar[Date] <= DATE(2021,12,31), 2

)

by this way you have filter for multiple scenarios and you can filter by your result as per visual