Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a Dataset called "Pipeline" and key column called "Project ID" , a column called "Status" and a column called "Date Update".
Project ID | Status | Date Updated |
11 | Pending | 02/09/2020 |
12 | Pending | 02/09/2020 |
13 | Approved | 02/09/2020 |
14 | Pending | 02/09/2020 |
12 | Pending | 08/09/2020 |
14 | Approved | 08/09/2020 |
12 | Approved | 15/09/2020 |
14 | Running | 15/09/2020 |
15 | Pending | 15/09/2020 |
16 | Pending | 15/09/2020 |
I would like to add a new column called, "Previous Status" that looks at the status of a project in the previous time period and add it next to the current status. It should be something like this:
Project ID | Status | Date Updated | Previous Status |
11 | Pending | 02/09/2020 | No record |
12 | Pending | 02/09/2020 | No record |
13 | Approved | 02/09/2020 | No record |
14 | Pending | 02/09/2020 | No record |
12 | Pending | 08/09/2020 | Pending |
14 | Approved | 08/09/2020 | Pending |
12 | Approved | 15/09/2020 | Pending |
14 | Running | 15/09/2020 | Approved |
15 | Pending | 15/09/2020 | No record |
16 | Pending | 15/09/2020 | No record |
Please note that the date when the dates when the dataset is updated are quite random.
I did this before for the situation when the dataset was updated Monthly, but that does not work anymore with the dataset being updated at random dates. Please see the previous code below:
Solved! Go to Solution.
Hi @Anonymous ,
Try to create a calculated column like so:
Previous Status =
VAR PreviousMaxDate =
CALCULATE (
MAX ( Pipeline[Date Updated] ),
FILTER (
Pipeline,
Pipeline[Project ID] = EARLIER ( Pipeline[Project ID] )
&& Pipeline[Date Updated] < EARLIER ( Pipeline[Date Updated] )
)
)
VAR PreviousStatus =
CALCULATE (
MAX ( Pipeline[Status] ),
FILTER (
Pipeline,
Pipeline[Project ID] = EARLIER ( Pipeline[Project ID] )
&& Pipeline[Date Updated] = PreviousMaxDate
)
)
RETURN
IF ( ISBLANK ( PreviousStatus ), "No record", PreviousStatus )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try to create a calculated column like so:
Previous Status =
VAR PreviousMaxDate =
CALCULATE (
MAX ( Pipeline[Date Updated] ),
FILTER (
Pipeline,
Pipeline[Project ID] = EARLIER ( Pipeline[Project ID] )
&& Pipeline[Date Updated] < EARLIER ( Pipeline[Date Updated] )
)
)
VAR PreviousStatus =
CALCULATE (
MAX ( Pipeline[Status] ),
FILTER (
Pipeline,
Pipeline[Project ID] = EARLIER ( Pipeline[Project ID] )
&& Pipeline[Date Updated] = PreviousMaxDate
)
)
RETURN
IF ( ISBLANK ( PreviousStatus ), "No record", PreviousStatus )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Worked as magic! Thanks!
Hi Maybe you can create a virtual table like this :
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |