Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm struggling trying to obtain the last status for an id.
The data I have is as follows:
FACT TABLE (Table1):
Id | Status | Date |
1 | Apples | 1/10/2018 |
1 | Bananas | 1/20/2018 |
1 | Grapes | 3/18/2018 |
2 | Bananas | 2/18/2018 |
2 | Apples | 4/10/2018 |
2 | Grapes | 5/10/2018 |
3 | Grapes | 1/10/2018 |
3 | Bananas | 3/12/2018 |
Calendar:
Date | YearMonth |
1/1/2018 | Jan-18 |
… | … |
31/1/2018 | Jan-18 |
… | … |
30/05/2018 | May-18 |
The result I want to obtain is the following:
Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 | |
Apples | 0 | 0 | 0 | 1 | 0 |
Bananas | 1 | 2 | 2 | 1 | 1 |
Grapes | 1 | 1 | 1 | 1 | 2 |
I'm able to obtain the last date:
Hi @andreabrezmes ,
Could you please offer more information about the logic of distinct count of last status over time?
For status "Apples", it is shown in 2018/1/10, but it seemed not show in your result table:
Regards,
Daniel He
Hi Daniel,
I would like to obtain the last status by month.
Last status for id 1 is "bananas" for january, so "apples" becomes 0. In February we have 2 id's with status "bananas" and in March id 1 becomes "grapes" and id 2 and 3 are "bananas".
Thanks,
Andrea.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |