Forum Discussion

mesuyog's avatar
mesuyog
Regular Visitor
4 years ago
Solved

Custom date filter

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 : ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Pbiuserr
Post Prodigy
4 years ago

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's avatar
    mesuyog
    Regular Visitor
    4 years ago

    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's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      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