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 : 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. 

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.

5 Replies

  • 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

  • Pbiuserr's avatar
    Pbiuserr
    Post Prodigy
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.