Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a following sample table with ProjectID, Milestone name and milestone date. I need generate a new table or modify the existing table which generates with months in between two milestone, and populate milestone names till the next milestone occurs, by ProjectID.
Sample:
ProjectID | Milestone Name | Milestone Date |
M001 | TROP | Jan-21 |
M001 | EFS | Jun-21 |
M001 | ELS | Jun-22 |
M001 | OLS | Jan-23 |
M001 | Lock | Mar-23 |
M001 | RCS | Aug-23 |
M002 | TROP | Feb-21 |
M002 | EFS | Apr-21 |
M002 | ELS | Jul-22 |
M002 | OLS | Jan-23 |
M002 | Lock | Mar-23 |
M002 | RCS | Sep-23 |
Expected output:
ProjectID | MonthCal | Milestone Name |
M001 | Jan-21 | TROP |
M001 | Feb-21 | TROP |
M001 | Mar-21 | TROP |
M001 | Apr-21 | TROP |
M001 | May-21 | TROP |
M001 | Jun-21 | EFS |
M001 | Jul-21 | EFS |
M001 | Aug-21 | EFS |
M001 | Sep-21 | EFS |
M001 | Oct-21 | EFS |
M001 | Nov-21 | EFS |
M001 | Dec-21 | EFS |
M001 | Jan-22 | EFS |
M001 | Feb-22 | EFS |
M001 | Mar-22 | EFS |
M001 | Apr-22 | EFS |
M001 | May-22 | EFS |
M001 | Jun-22 | ELS |
M001 | Jul-22 | ELS |
M001 | Aug-22 | ELS |
M001 | Sep-22 | ELS |
M001 | Oct-22 | ELS |
M001 | Nov-22 | ELS |
M001 | Dec-22 | ELS |
M001 | Jan-23 | OLS |
M001 | Feb-23 | OLS |
M001 | Mar-23 | LOCK |
M001 | Apr-23 | LOCK |
M001 | May-23 | LOCK |
M001 | Jun-23 | LOCK |
M001 | Jul-23 | LOCK |
M001 | Aug-23 | RCS |
M002 | Feb-21 | TROP |
M002 | Mar-21 | TROP |
M002 | Apr-21 | EFS |
M002 | May-21 | EFS |
M002 | Jun-21 | EFS |
M002 | Jul-21 | EFS |
M002 | Aug-21 | EFS |
M002 | Sep-21 | EFS |
M002 | Oct-21 | EFS |
M002 | Nov-21 | EFS |
M002 | Dec-21 | EFS |
M002 | Jan-22 | EFS |
M002 | Feb-22 | EFS |
M002 | Mar-22 | EFS |
M002 | Apr-22 | EFS |
M002 | May-22 | EFS |
M002 | Jun-22 | EFS |
M002 | Jul-22 | ELS |
M002 | Aug-22 | ELS |
M002 | Sep-22 | ELS |
M002 | Oct-22 | ELS |
M002 | Nov-22 | ELS |
M002 | Dec-22 | ELS |
M002 | Jan-23 | OLS |
M002 | Feb-23 | OLS |
M002 | Mar-23 | LOCK |
M002 | Apr-23 | LOCK |
M002 | May-23 | LOCK |
M002 | Jun-23 | LOCK |
M002 | Jul-23 | LOCK |
M002 | Aug-23 | LOCK |
M002 | Sep-23 | RCS |
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
12 |