Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have a table like this
Status | Customer | Customer ID | Year | Date |
OK | WW | WW-1 | 2022 | 01.02.2022 12:45 |
Pending | WW | WW-1 | 2023 | 01.02.2023 14:45 |
Pending | WW | WW-1 | 2023 | 01.08.2023 14:45 |
N/A | YY | YY-3 | 2023 | 15.12.2023 01:45 |
OK | AA | AA-1 | 2024 | 01.01.2024 12:25 |
OK | AA | AA-1 | 2024 | 05.01.2024 13:25 |
I want to either create a measure or a flag-colum that:
- Finds the last status that was entered in the table the following year
The table should look like this:
Status | Customer | Customer ID | Year | Date |
Pending | WW | WW-1 | 2022 | 01.02.2022 12:45 |
Pending | WW | WW-1 | 2023 | 01.08.2023 14:45 |
N/A | YY | YY-3 | 2023 | 15.12.2023 01:45 |
OK | AA | AA-1 | 2024 | 05.01.2024 13:25 |
in 2022, customer WW was chnaged only once
in 2023, customer WW was changed twice but last time was 01.08.2023
in 2023 YY was chnged once
in 2024, AA was chnaged twice but last time was 05.01.2024
Dear @Idrissshatila
Thank you.
It sort of gave me what i wanted.
What i forgot to mention was that there are other fields that will or will not change value(s) between each new line:
Status | Customer-ID | Membership status | Comments | Status | Year | ChangeDate |
OK | WW | 0 | Considering doubling | 1 | 2022 | 01-01-2022 00:01 |
OK | WW-1 | 0 | x | 1 | 2023 | 01-01-2023 11:00 03:00 |
Pending | WW-2 | 1 | x | 999 | 2022 | 01-01-2022 00:00 |
Pending | WW-1 | 0 | x | 999 | 2023 | 02-01-2023 03:00 |
N/A | WW-2 | 3 | x | 0 | 2024 | 01-01-2023 4:00 |
OK | WW | 0 | Considering trippling | 1 | 2023 | 01-01-2023 12:00 |
OK | WW | x | Considering ending | 1 | 2023 | 02-01-2023 12:00 |
In the table above you can see that for the customers, sometimes the values remain the same but ChangeDate varies.
If i only apply Latest to date, i still get more than one record because other values are same.
I have to apply LATEST on all the columns
Hello @itsAftab
you add all the fields in a table and set the date to the latest as in the screenshot.
and here is the pbix file attached for your reference.
Proud to be a Super User! | |