Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Compare Values in a Table filtered by Max Date and Min Date

    Posted Jul 01, 2022 11:32 AM

    Hopefully i can explain what i am trying to achieve. I have a big table with lots of entries from a monthly extract, simplified example below: What i would like to do is identify rows which have been Added and rows which have been Removed, based on min date and max date. In the example below, the min date is Jan-22 and max date is April-22. The result is John has been removed, Mary, Bruce, Sheila and Jane have been Added

    However, the min and max date could change with a Slicer. I thought having an 'Added' and 'Removed' column would be the most straight forward way to present the data...

    TABLE: Data
    Date Location Unique Name Fruit Added Removed
    Jan-22 UK John Apple Yes
    Jan-22 UK Paul Pear
    Feb-22 UK John Apple
    Feb-22 UK Paul Pear
    Feb-22 North America Mary Apple
    Feb-22 ASPAC Bruce Orange
    Mar-22 UK John Apple
    Mar-22 UK Paul Pear
    Mar-22 North America Mary Apple
    Mar-22 ASPAC Bruce Orange
    Mar-22 ASPAC Sheila Banana
    Apr-22 UK Paul Pear
    Apr-22 North America Mary Apple Yes
    Apr-22 ASPAC Bruce Orange Yes
    Apr-22 ASPAC Sheila Banana Yes
    Apr-22 UK Jane Apple Yes

    If not possible with Dax if there something i could do in Power Query?

  • 2.  RE: Compare Values in a Table filtered by Max Date and Min Date

    Gold Contributor
    Posted Jul 02, 2022 10:14 AM
      |   view attached
    I have done this before a while back but used power query. This blog post can explain the logic https://exceed.hr/blog/how-to-track-changes-in-the-table-in-power-query/

    I have also attached an Excel file which contains a function that compares 2 tables which I feel can be beneficial .

    Shungu Dhlamini
    Power BI Consultant