Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter data with last 15 months

Hello All,
I want to filter data with last 15 months of data from max date
Please help

Thanks!

Need Help
Show and Tell
Tips and Tricks
  • fahadqadir3's avatar
    fahadqadir3
    2 years ago

    Anonymous  You can create a new Custom Column in Power Query to present months difference from Max Date Month by using the following Power Query Formula

    Date.Month([Date]) - Date.Month(List.Max(#"Sorted Rows"[Date])) + 
    (Date.Year([Date]) - Date.Year(List.Max(#"Sorted Rows"[Date]))) * 12

    Then apply a Filter on new column where months difference is greater than -15, so that only last 15 months data should be availble to use.

     

    Regards,

    Fahad Qadir

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

2 Replies

  • kpost's avatar
    kpost
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Are you meaning the last 15 calendar months?  Or are you meaning (15months)*(~30days/month) = ~450 days?

    Are you wanting this as a DAX measure or a visual-level filter or a page-level filter or a report-level filter?  Are you wanting to screen out data older than 15 months at the import level using Power Query?


    When you say "max date" are you referring to a date selected in a slicer, or are you referring to today's date (whatever that happens to be), or are you referring to the latest date found in a certain table?

     

    More information about the exact problem you're trying to solve is going to help us answer your question.  As much as you can provide.

     

    ///Mediocre Power BI Advice, but it's free///

  • fahadqadir3's avatar
    fahadqadir3
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Anonymous  You can create a new Custom Column in Power Query to present months difference from Max Date Month by using the following Power Query Formula

    Date.Month([Date]) - Date.Month(List.Max(#"Sorted Rows"[Date])) + 
    (Date.Year([Date]) - Date.Year(List.Max(#"Sorted Rows"[Date]))) * 12

    Then apply a Filter on new column where months difference is greater than -15, so that only last 15 months data should be availble to use.

     

    Regards,

    Fahad Qadir

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!