Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic filter on month number based on current date

Hi all,

 

I have a table containing sales and fc per month on an item level.

 

 

I'd like to only include data in my visuals of months that are already finished. Furthermore i'd like to limit the amount of months shown, for example the last 3 or 6 months, based on the current date.

 

Can someone help me how to set this up?

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Yes, I got the issue after changing the datatype of column to Date. 

     

    I wanted to know if you can add more detail level information such as date or year(may be dummy) so that the column can calculate accordingly. 

     

    Relative date filter is an easy option but it is applied to only date datatype column. So may be you can add dummy date such as 1/1/2019, 2/1/2019 etc... 

     

    Otherwise, you might have to write a logic to display last N months from selected month, which can be little tricky here

     

    Here are few links I found which might help you.

     

    http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-power-bi.html

     

    I will let you know in case I come up with any logic

     

    Thanks,

    Vaibhavi

     

     

     

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I tried to do this by creating a measure which will calculate current month and a new column which will check if your Month number is less than (or equal to) current month. 

     

    Measure - 

    CurrentMonth = MONTH(TODAY()) -- This will always give you current month number
     
    Column - 
    MonthCheck = IF(Sheet1[MonthNr] <= [CurrentMonthDAX],Sheet1[MonthNr],0)
     
    Then I changed the datatype of MonthCheck column to date and pulled this measure in filter pane, I used Relative date filtering to show last 3 months ( you can set anything you want to)
     
    I hope this will help in answering your question. Please let me know in case you have any questions
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      I think I understand what you mean.

       

      However, can you explain how you format monthnumber to date?

       

      When I format this to date it gives me 1 - 11 januari 1900. Which is logical since i format an integer as date.

       

      How to do this correctly?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Yes, I got the issue after changing the datatype of column to Date. 

         

        I wanted to know if you can add more detail level information such as date or year(may be dummy) so that the column can calculate accordingly. 

         

        Relative date filter is an easy option but it is applied to only date datatype column. So may be you can add dummy date such as 1/1/2019, 2/1/2019 etc... 

         

        Otherwise, you might have to write a logic to display last N months from selected month, which can be little tricky here

         

        Here are few links I found which might help you.

         

        http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-power-bi.html

         

        I will let you know in case I come up with any logic

         

        Thanks,

        Vaibhavi