Forum Discussion

HuyNguyenn's avatar
HuyNguyenn
New Member
1 year ago
Solved

Filter date based on other table

Hi Experts,  I have one main table contains data. It looks like this Also I have 2nd table only contains EndOfMonth date What I want is to filter the main table only to show the End of...
Need Help
Tips and Tricks
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi HuyNguyenn,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you Deku for the prompt response.

     

    To filter the Main Table based on the EndOfMonth Table follow below steps:

    • Add a Year column to the EndOfMonth table and sort the EndOfMonth table by Year (Descending) and EndOfMonth (Descending).
    • Group by Year, then add an Index Column that starts from 1 inside each year.
    • Filter the table to keep only as Index = 1 (End of the current month), Index = 2 (End of last month), for March 31 of last year, use Date.Month([EndOfMonth]) = 3 and Date.Day([EndOfMonth]) = 31
    • Merge the filtered EndOfMonth table with the Main Table like by matching TransDate to EndOfMonth.

    This will filter the Main Table to show only the required end-of-month dates.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

HuyNguyenn's avatar
HuyNguyenn
New Member
1 year ago

Dear Deku,

 

Thanks for your prompt reply.

 

I have added the Index, but when filter, I get 2017 and 2018 data

 

Sorry as I'm new to PBI 

v-achippa's avatar
v-achippa
Community Support
1 year ago

Hi HuyNguyenn,

 

Thank you for reaching out to Microsoft Fabric Community.

 

Thank you Deku for the prompt response.

 

To filter the Main Table based on the EndOfMonth Table follow below steps:

  • Add a Year column to the EndOfMonth table and sort the EndOfMonth table by Year (Descending) and EndOfMonth (Descending).
  • Group by Year, then add an Index Column that starts from 1 inside each year.
  • Filter the table to keep only as Index = 1 (End of the current month), Index = 2 (End of last month), for March 31 of last year, use Date.Month([EndOfMonth]) = 3 and Date.Day([EndOfMonth]) = 31
  • Merge the filtered EndOfMonth table with the Main Table like by matching TransDate to EndOfMonth.

This will filter the Main Table to show only the required end-of-month dates.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thanks and regards,

Anjan Kumar Chippa

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi HuyNguyenn,

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
    If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      1 year ago

      Hi @HuyNguyenn,

       

      We wanted to kindly follow up to check if the solution I have provided for the issue worked.
      If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi @HuyNguyenn,

         

        As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
        If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

         

        Thanks and regards,

        Anjan Kumar Chippa