Check previous records for change / measure days in each bucket (MS Planner Data)

Check previous records for change / measure days in each bucket (MS Planner Data)Follow
Richard McClanahan
Richard McClanahanAug 30, 2022 03:19 PM
Hello all, I have an SPO list as a data set, I populate this list via workflow, the workflow compares …
1. Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 30, 2022 03:19 PM
Edited by Richard McClanahan Aug 30, 2022 03:25 PM
Hello all, I have an SPO list as a data set, I populate this list via workflow, the workflow compares current data to new data and adds any changed records to the list. There are 2 types of changes, the task can move to a new Bucket like from Eval to S1 or S1 to S2. The other type of change is when a Checklist item completes. What I am trying to accomplish is to capture the number of days each task spends in a bucket as well as the days it takes to complete a Checklist item. I have had limited success and all the solutions I have tried depend on the previous row being the previous date. The dax below works but only when 2 dates exist. I am new to table expressions and really have no idea of the best way to do this. For some reason I can’t get the Excel file to upload? any suggestions, it’s only 431 rows
thanks for any help

PreviosNameDays =
var _Last = MAX(Table[Created])
var _MinDate = CALCULATE (
MIN( Table[Created]),
ALLEXCEPT( ‘Table’, ‘Table'[BucketID] ))
var _previousBKT = CALCULATE(MAX(‘Table'[BucketID]),Table[Created])
var _currentBKT = CALCULATE(MIN(Table[BucketID]),Table[created])
return
IF(_previousBKT =_currentBKT && _MinDate <> _Last,VALUE(UTCNOW()-_MinDate),
IF(_previousBKT =_currentBKT && _MinDate = _Last,VALUE(UTCNOW()-_MinDate),DATEDIFF(_MinDate,_Last,DAY)))
TaskChkListUID TaskID BucketID Created
29970197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
34619197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
44309197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
46815197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
57416197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
83616197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
90051197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
90647197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
94340197-A5dtnE62rWN_R8jq1YIAEXwp 197-A5dtnE62rWN_R8jq1YIAEXwp fKs7FXWuN0C_2PJx9GsPQIIAHNeF 7/27/2022 0:00
299702oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
346192oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
443092oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
468152oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
574162oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
836162oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
900512oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
906472oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
943402oiZTMeKnU2T_aWa8YDQ6YIACcHk 2oiZTMeKnU2T_aWa8YDQ6YIACcHk PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
299703k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
346193k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
443093k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
468153k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
574163k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
836163k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
900513k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
906473k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
943403k8m9LUwQUmRQCkL32SOT4IADz1a 3k8m9LUwQUmRQCkL32SOT4IADz1a FZQB41MS6U256wmEXIrYroIAGlSY 7/27/2022 0:00
299704o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
299704o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
346194o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
346194o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
443094o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
443094o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
468154o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
468154o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
574164o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
574164o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
836164o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
836164o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
900514o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
900514o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
906474o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
906474o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
943404o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En FZQB41MS6U256wmEXIrYroIAGlSY 8/9/2022 0:00
943404o_xrb0IOU29UHkQ408RR4IAF5En 4o_xrb0IOU29UHkQ408RR4IAF5En jETjBvMEO0Oy4VTXHq5PC4IAEelR 7/27/2022 0:00
299707lD-20saaEKGs8F5cfNrXIIAFEqp 7lD-20saaEKGs8F5cfNrXIIAFEqp PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00
346197lD-20saaEKGs8F5cfNrXIIAFEqp 7lD-20saaEKGs8F5cfNrXIIAFEqp PLdL2_J7SkKdpsN4s31TxYIABWAI 7/27/2022 0:00

——————————
Richard McClanahan
95628
Fair Oaks
——————————

2. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Aug 30, 2022 05:39 PM
@Richard McClanahan,
Your data looks pretty complex, it’s hard to give you any suggestions without seeing the data, and the data model. Just looking at what you posted, it seems that you might need to start by breaking the table into two tables: one that contains the TaskID, TaskChecklistID, and Date; and a second one that contains ​TaksId, BucketID, and Date. And remove all the duplicate rows from each of them. You also might need to have a separate table that just has a list of the unique TaskIDs, and then in your data model you would connect each of the first two tables to the TaskID table. Just some ideas to consider. If you can figure out how to post your .pbix file, with some example data, then I think you will get someone to dig in and help.

Kaz.​

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

3. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 30, 2022 05:53 PM
Any ideas why the upload file feature is not working for me? I tried several times, both when I created the post and just now in the reply.

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

4. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 30, 2022 05:59 PM
Can you copy and paste the data from above? it looks much better when pasted into a table in Excel. I will try to figure out why I can’t upload.

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

5. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Aug 30, 2022 06:21 PM
@Richard McClanahan,
Actually I did exactly that (copy and paste from your message to excel). If that’s all your data, then maybe you could provide some more explanation, because I’m having trouble understanding it. For example, could you provide a sample output where you have calculated the measure you want by hand, and then point us to the rows in the dataset that should be used to perform that calculation?

Thanks,
Kaz.

p.s., I have no idea why you are unable to post – you might see if there is some technical support for the site. I’m just a fellow user, just like you.​

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

6. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 30, 2022 06:51 PM
Edited by Richard McClanahan Aug 30, 2022 07:56 PM
Thanks for looking into it, there are 10 buckets, each task (currently 40 but more to come) spends X amount of days in a bucket, the outcome below works only because there is only 1 or 2 dates for each record, as soon as I add an updated record there will be a third date, then fourth and so on. I need to compare the new record to the newest record not Min or First record. There are no dups, each task has 9 checklist items, the Checklist Item ID and the Task ID combined make the unique identifier. Planner record unique ID is not a field I can pull with the Flow action so I created one.

 

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

7. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 30, 2022 07:54 PM
This would also work great if I had a column or measure to calculate the time spent in each Bucket (Stage). This matrix is a good example of my intended outcome but with DateDiff Days as the value

 

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

8. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Aug 30, 2022 08:25 PM
Edited by Kaz Shakir Aug 30, 2022 08:27 PM | view attached
@Richard McClanahan,
Those descriptions helped, but I’m not sure I still have the full picture. Regardless, I tried to create an example. Let’s start with a simplified example, and then if you tell me we are on the right track, then we can make it a little more complex.

Let’s say that we have a table called “TaskBucket” that looks like this:
​
And then a second table called “Tasks” that looks like this:

And our data model looks like this:

Then we can create calculated columns in the Tasks table that determine when each Bucket starts, like this:
Bucket 1 Start =
VAR _Bucket1 =
FILTER(
TaskBucket,
TaskBucket[BucketID] = “1” && TaskBucket[TaskID] = Tasks[TaskID]
)
RETURN
CALCULATE(
MIN(TaskBucket[CreatedDate]),
_Bucket1
)​

Then, let’s assume that the buckets occur in a specific order. In other words, Bucket 1 happens first, then Bucket 2, and then Bucket 3. In that case we can determine the end of each bucket, as the start of the next bucket:
Bucket 1 End = Tasks[Bucket 2 Start]​

And then the amount of time spent in that Bucket would simply be:
Bucket 1 Duration =
DATEDIFF(Tasks[Bucket 1 Start], Tasks[Bucket 1 End], DAY)​

And the resulting output:
I’m attaching the simple .pbix I created. Maybe you could take it an make it more representative of your dataset, and re-post, and then talk about what needs to change.

Kaz.

 

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixmcclanahanExample_v1.pbix

9. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 30, 2022 08:51 PM
I added my data but still no luck with the upload, Buckets can happen out of order, like being on hold. I am going to try to upload from my personal PC. Thanks again Kaz

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

10. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Aug 31, 2022 10:15 AM
I simplified the data, I want to track the number of days that each task spends in each bucket, and the number of days to complete a checklist item (from false to True). The logic is, if there is no change then count the days from 7/27 to Today, if there is a change then count the previous days in bucket and the current days in Bucket

ID TaskName BucketName CheckListID CompletedChecklistItems Created
2310 AS S3 29970 FALSE 7/27/2022
2311 AS S3 34619 FALSE 7/27/2022
2312 AS S3 44309 FALSE 7/27/2022
2313 AS S3 46815 FALSE 7/27/2022
2314 AS S3 57416 FALSE 7/27/2022
2315 AS S3 83616 FALSE 7/27/2022
2316 AS S3 90051 FALSE 7/27/2022
2317 AS S3 90647 FALSE 7/27/2022
2318 AS S3 94340 FALSE 7/27/2022
2601 AS ST 29970 FALSE 8/9/2022
2602 AS ST 34619 FALSE 8/9/2022
2603 AS ST 44309 FALSE 8/9/2022
2604 AS ST 46815 FALSE 8/9/2022
2605 AS ST 57416 FALSE 8/9/2022
2606 AS ST 83616 FALSE 8/9/2022
2607 AS ST 90051 FALSE 8/9/2022
2608 AS ST 90647 FALSE 8/9/2022
2609 AS ST 94340 FALSE 8/9/2022
2454 BC S3 29970 TRUE 7/27/2022
2455 BC S3 34619 TRUE 7/27/2022
2456 BC S3 44309 FALSE 7/27/2022
2457 BC S3 46815 FALSE 7/27/2022
2458 BC S3 57416 FALSE 7/27/2022
2459 BC S3 83616 FALSE 7/27/2022
2460 BC S3 90051 FALSE 7/27/2022
2461 BC S3 90647 FALSE 7/27/2022
2462 BC S3 94340 TRUE 7/27/2022
3982 BC S3 90051 TRUE 8/17/2022
2130 RC S1 29970 FALSE 7/27/2022
2131 RC S1 34619 TRUE 7/27/2022
2132 RC S1 44309 FALSE 7/27/2022
2133 RC S1 46815 FALSE 7/27/2022
2134 RC S1 57416 FALSE 7/27/2022
2135 RC S1 83616 FALSE 7/27/2022
2136 RC S1 90051 FALSE 7/27/2022
2137 RC S1 90647 FALSE 7/27/2022
2138 RC S1 94340 FALSE 7/27/2022
2646 RC S2 29970 FALSE 8/10/2022
2647 RC S2 34619 TRUE 8/10/2022
2648 RC S2 44309 FALSE 8/10/2022
2649 RC S2 46815 FALSE 8/10/2022
2650 RC S2 57416 FALSE 8/10/2022
2651 RC S2 83616 FALSE 8/10/2022
2652 RC S2 90051 FALSE 8/10/2022
2653 RC S2 90647 FALSE 8/10/2022
2654 RC S2 94340 FALSE 8/10/2022
3984 RC S3 29970 FALSE 8/29/2022
3985 RC S3 34619 TRUE 8/29/2022
3986 RC S3 44309 FALSE 8/29/2022
3987 RC S3 46815 FALSE 8/29/2022
3988 RC S3 57416 FALSE 8/29/2022
3989 RC S3 83616 FALSE 8/29/2022
3990 RC S3 90051 FALSE 8/29/2022
3991 RC S3 90647 FALSE 8/29/2022
3992 RC S3 94340 FALSE 8/29/2022

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

11. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Sep 01, 2022 06:35 PM
Edited by Kaz Shakir Sep 01, 2022 06:44 PM | view attached
@Richard McClanahan,
First, I was thinking about your trouble upl​oading a file – I noticed that when I upload something my browser uses a pop-up window to request the file. If your browser is blocking pop-ups, that might be the issue. Just guessing, but something to check on.

Regarding your project, I have a partial answer for you. I just focused on the Bucket’s for now.

This step may not be needed, but this is the way I like to have my data arranged: kind of a star-schema. In that structure, the table you pasted in your last message is the Fact table, and from it, I created several Dimension tables: one for Tasks, one for Buckets, and one for Checklist items (you can see those steps in the Transform Data section of the attached file). And then I connect those to the fact table in the data model, like this:

Then, I also like to create a table that will hold all of my measures. I do this by selecting the “New Table” item under “Modeling”, and just make it equal to zero:

Now, for this project I created three measures: Bucket Start Date, Bucket End Date, and Bucket Duration.

Bucket Start Date is pretty straight forward, it just looks in the fact table and finds the earliest date for each task – bucket combination (if one exists). The first two VAR statements just grab the current bucket and current task in the given filter context, and then it returns the earliest date for that combination:
Bucket Start Date =
VAR _currentBucket =
MIN(FactTable[BucketID])
VAR _currentTask =
MIN(FactTable[TaskID])
RETURN
CALCULATE(
MIN(FactTable[Created]),
FILTER(
ALL(FactTable),
FactTable[BucketID] = _currentBucket && FactTable[TaskID] = _currentTask
)
)​
And, Bucket Duration is also straight forward, it’s simply the DATEDIFF between the start and end dates:

Bucket Duration =
DATEDIFF([Bucket Start Date], [Bucket End Date], DAY)
Bucket End Date is a little more complicated, because there is not actually a bucket end date in the Facts. Therefore, I had to make an assumption, and my assumption is that the end date of the current Bucket is whenever the next Bucket starts. Now I don’t make any assumptions about the ordering of the Buckets, I just let the formula figure out what bucket is next. And if there is no “next Bucket” (in which case the formula would result in a blank), I replace it with UTCNOW() to make it today:

Bucket End Date =
VAR _currentBucket =
MIN(FactTable[BucketID])
VAR _currentTask =
MIN(FactTable[TaskID])
VAR _currentStart =
[Bucket Start Date]
RETURN
IF(
ISBLANK(_currentStart),
BLANK(),
COALESCE(
CALCULATE(
MIN(FactTable[Created]),
FILTER(
ALL(FactTable),
FactTable[BucketID] <> _currentBucket && FactTable[TaskID] = _currentTask &&
FactTable[Created] > _currentStart
)
),
UTCNOW()
)
)
And putting this info into a simple table, it looks like this:
I’m attaching the .pbix file so you can take a look at the details of what I did.

Now, with regards to the checklist items, I need some more information: from the data you have, how do you determine when a checklist item was started, and when it was finished? From your example data, all I could tell is that either an item was finished or not finished, but I had no information about when it was started.

Hope this is helpful. Please let me know if this the right direction for you.

Kaz.

 

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixmcclanahanExample_v2.pbix

12. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 01, 2022 08:22 PM
First of all, thank you for taking the time. a little history on the data. The project had been going for 8 months or so before the very first export on 7/27/2022, a lot happened that will not be captured. there are 40 customers, 9 checklist items for each customer and each customer passes through the 11 buckets. For the checklist items, All I can do at this point is measure “Not Completed” from 7/27 until Today(), and as soon as they complete that’s the end date.

As far as the Buckets go, current bucket would be from the Max date to Today()​. I will spend some time with your pbix after the holiday.

Nice work and thanks again

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

13. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 07, 2022 10:50 AM
Kaz, you solution works perfect, I used “Bucket Duration” in a matrix table and it works exactly the way I wanted.

To add Checklist items to the model do I follow the steps you took with Tasks and buckets (merge it and add the index)? or just use the checklist ID that exists in the Fact Table.

Thanks

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

14. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Sep 07, 2022 05:51 PM
@Richard McClanahan,
Yes, just use the checklist ID that you already have in the Fact table. Just make sure that value is unique – you can’t have duplicate rows in the Dimension table. That was the only reason for adding the index column in the other dimension tables – to make sure each row was unique.

I’m glad that solution worked for you. Please let me know if you have any more questions.

Kaz.​

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

15. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 07, 2022 09:28 PM
Kaz, I tried to get the Duration between the Check list item “Not Completed” (False) and “Completed”(True) by modifying your Bucket measures but the results are inconsistent/wrong. The logic would be,

If it does change from false to True, measure the days between. I tried to filter on Not Completed but doesn’t seem to be working. I think the problem is, the bucket change dates are included, at one point I thought of adding a flag that would identify bucket change and checklist change. Thanks for the help, maybe you can help me fix the start and I can try it from there

CheckList Start =
VAR _currentChkList =
MIN(FactTable[ChkListIndex])
VAR _currentTask =
MIN(FactTable[TaskIndex])
VAR _checkstatus = CALCULATE(MIN(FactTable[Completed Checklist Items]),FactTable[Completed Checklist Items] = “Not Completed”)
RETURN
CALCULATE(
MIN(FactTable[Created]),
FILTER(
ALL(FactTable),
FactTable[ChkListIndex] = _currentChkList && FactTable[TaskIndex] = _currentTask && FactTable[Completed Checklist Items] =_checkstatus
)
)

 

 

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

16. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Sep 08, 2022 06:58 PM
@Richard McClanahan,
I think that it might be simpler than what you are attempting. Will this work?
CheckList Start Date =
VAR _currentCheckList =
MIN(FactTable[CheckListID])
VAR _currentTask =
MIN(FactTable[TaskID])
RETURN
CALCULATE(
MIN(FactTable[Created]),
FILTER(
ALL(FactTable),
FactTable[TaskID] = _currentTask && FactTable[CheckListID] = _currentCheckList && FactTable[CompletedChecklistItems] = FALSE()
)
)​
​
Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

17. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 08, 2022 07:56 PM
The end date is still returning Bucket Change dates on some records, I have not figured out how to differentiate or exclude bucket change from Checklist item change, your solution excluded checklist change from Bucket change? how did you accomplish that?

One scenario that causing me issues is as follows,

7/27/22 Original = Bucket Name is “S1”, 9 CheckList Items = False
8/10/22 1st update = Bucket Name is “S2”, 9 CheckList Items = False, Bucket Change, 9 records added, no checklist change
8/17/22 2nd Update = Bucket Name is “S2”, 2 CheckList Items = True, Checklist Change, 2 records added, no bucket change
9/6/22 3rd Update = Bucket Name is “S3”, 6 CheckList Items = True, Bucket Change and Checklist Change, 9 records added, 6 of the 9 have a checklist change

What about another table to Store CheckList Changes? Thanks Kaz

CheckList End =
VAR _currentCheck =
MIN(FactTable[ChkListIndex])
VAR _currentTask =
MIN(FactTable[TaskIndex])
VAR _currentStart =
Measures[CheckList Start]
RETURN
IF(
ISBLANK(_currentCheck),
BLANK(),
COALESCE(
CALCULATE(
MIN(FactTable[Created]),
FILTER(
ALL(FactTable),
FactTable[ChkListIndex] <> _currentCheck && FactTable[TaskIndex] = _currentTask &&
FactTable[Created] > _currentStart && FactTable[Completed Checklist Items] = “Completed”
)
),
UTCNOW()
)
)

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

18. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Sep 09, 2022 06:21 PM
@Richard McClanahan,
The logic we used for the bucket end does not work for the check list items. With buckets, we were able to say that once the task moves from the first bucket to the second bucket, then the ​end date for the first bucket, must be the start date of the second bucket. However, with checklist items this logic does not work – we start the checklist all at once – meaning that all items really have the same start date; and we can finish any of them at any point, even simultaneously.

So, the formula to the Checklist End Date is probably much simpler than that for the buckets; and is nearly identical to the formula we used for the start dates. Here’s what I would use:
CheckList End Date =
VAR _currentCheckList =
MIN(FactTable[CheckListID])
VAR _currentTask =
MIN(FactTable[TaskID])
RETURN
CALCULATE(
MIN(FactTable[Created]),
FILTER(
ALL(FactTable),
FactTable[TaskID] = _currentTask && FactTable[CheckListID] = _currentCheckList && FactTable[CompletedChecklistItems] = TRUE()
)
)​

You also asked another question – “What about another table to Store CheckList Changes?” That might make sense. I took a look at the Planner app (I don’t use it currently), but noticed that the checklist items are actually a part of the task. And the checklist items really have nothing to do with the buckets. As a result it might make sense to have two fact tables – one that has information about Tasks and Buckets (this would give you information about how a task moves through the buckets), and another table for Tasks and Checklist items (this would give you information about the completion status of the checklist). Having two fact tables might require changing some of the measures, but it might be a more logical structure.

Kaz.

 

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

 

19. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 15, 2022 11:47 AM
Edited by Richard McClanahan Sep 15, 2022 11:50 AM
Kaz, thanks so much, I followed your direction and put all the measures in a table, so much more organized. I do have another question, the measures for Bucket Duration and CheckList duration only seem to work in a table? if I try and use the duration measure in a card (Grand Total duration) or an AVERAGE I get nothing. I tried creating columns but cant get the end date right, is this the right approach for the start column? seems to return the correct date

colChkStart = CALCULATE(MIN(FactTable[Created]),ALLEXCEPT(FactTable,FactTable[TaskIndex]))

Also just wondering if you store your custom columns in the measures table or maybe create a custom columns table?

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

20. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Kaz Shakir
Posted Sep 16, 2022 07:03 PM
Edited by Kaz Shakir Sep 16, 2022 07:11 PM | view attached
@Richard McClanahan,
So the thing with any measure is that it re-calculates in the current filter context.​ For example, let’s create a new page that has a slicer on it for Task Name, and another for Bucket Name, and a card that shows the Bucket Duration. Then if we select Task = RC, and Bucket = S1, the card shows the correct Bucket Duration of 14 days:
This is because the filter context has meaningful information. The Tasks have been filtered to RC and the Buckets have been filtered to S1.
Remember that the formula for the Bucket Duration is simply to take the difference between the start date and end date for a given bucket. And without that filter context narrowed down to a single task and bucket, the formula is going to give you odd results. As an example let’s take a look at what happens when if we don’t select anything in either of the slicers:

The result for Bucket Duration is 13 – why? Let’s start by looking at the Bucket Start Date measure. The formula for that measure says give us the minimum date in the fact table where the bucket id is equal to the current bucket and the task id is equal to the current task. Since we have not selected a bucket in the slicer, the variable _currentBucket is going to see all of the buckets, and take the minimum of the bucket IDs – in our example file, Bucket S3 had the smallest ID; and since we have not selected a task in the slicer, the variable _currentTask is going to see all of the tasks, and will pick the task that has the minimum ID – in our sample file that would be task AS. Therefore, the Bucket Start Date that the measure returns is the start date for Task AS, and Bucket S3. Similarly, the Bucket End Date will do the same thing, and therefore the Bucket Duration that we see on the card is simply for Task AS, Bucket S3.

And why does this measure work correctly when we use it in a table, where there are no slicers involved? Because each row of the table acts like a set of slicers. So, in the first row of the table below, it’s the same as having a silcer for Task set to AS, and a slicer for Bucket set to S3; or in the last row the Task slicer is set to RC, and bucket slicer is set to S3.

So, how do we get an average duration for the whole table, while still being able to calculate the duration for each task? To accomplish this, we need to change the formula for the Bucket Duration, such that it will iterate through a table that has every combination of task and bucket, one row at a time and calculate the duration for each row, and then average all of the rows. That measure would look like this:
Average Bucket Duration =
VAR _tableOfTaskBucket =
DISTINCT(
SELECTCOLUMNS(
FactTable,
“Task”, FactTable[TaskID],
“Bucket”, FactTable[BucketID]
)
)
RETURN
AVERAGEX(
_tableOfTaskBucket,
[Bucket Duration]
)​
And, when you place that measure on the card, and no values are selected in the slicers, you get an answer that makes more sense:

And that measure works in the table context as well – on each row it still gives you the value for that combination of task and bucket, and the grand total, is the average of all those combinations of task and bucket.
Similarly, if you were to use SUMX instead of AVERAGEX in that measure, you would get the total sum of the values:

So, as you can see, you have to consider what sorts of information you want to show, and then design your measures to get to that information.

Your next question was about calculated columns – calculated columns have to live in a particular table in your data model; they can not exist in the measures table. A calculated column is a new column that is added to a specific table, and the formula you use would get recalculated for each row of that table.

Does that answer your questions?
Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixmcclanahanExample_v3.pbix

21. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 20, 2022 10:27 AM
Kaz, thanks so much for all the help, Average of bucket duration works great. I think the measure for total checklist item duration is working, although I’m not sure how to filter only completed checklist items, the average I get is too low using a slicer. Most of the checklist items are not completed and the more important average is the result of the completed items.
AvgCheckDuration =
VAR _tableOfTaskCheck =
DISTINCT(
SELECTCOLUMNS(
FactTable,
“Task”, FactTable[TaskIndex],
“CheckItem”, FactTable[ChkListIndex]

)
)
RETURN
AVERAGEX(
_tableOfTaskCheck,
[CheckItemDuration]
)

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

22. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 20, 2022 07:34 PM
Kaz, thanks for all the help and disregard the last post, I duplicated what you sent in the last pbix and the average is correct now. I really appreciate your explanations and expertise. Thanks again

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

23. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Mar 21, 2023 02:00 PM
Hello Kaz, are you still active on this site?

 

——————————
Richard McClanahan
95628
Fair Oaks
——————————

 

24. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Top Contributor
Sam Duval
Posted Sep 09, 2022 07:24 AM
I can’t help with the power BI, but I am curious what your workflow looks like for getting the planner data. Does it just capture all the data every day and compare day/day changes or is there something else you are able to capture showing what changed?

——————————
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
——————————

 

25. RE: Check previous records for change / measure days in each bucket (MS Planner Data)

Richard McClanahan
Posted Sep 09, 2022 12:53 PM
Sam, the planner workflow actions are limited but I am able to pull tasks, checklist items, completed checklist items, bucket name, labels and some other columns I don’t use but all the ID’s are available as well. There are more columns available in the workflow actions compared to the export to excel.

I have 2 SPO lists, primary and updates. the workflow below uses the SPO create item to push all the Planner data to the updates list, then I have a “Item is created” (not shown) workflow that compares the records in the updates list to the records in the primary list.
The filter looks for identical records like (UID eq UID) and (Bucket ID eq Bucket ID) or ( Completed Checklist item eq Completed Checklist item) if a match is not found, then a new record is created in the primary list and all the records that are not updates are deleted from the updates list.

The key to all this is the need for a UID generated from the Planner data, I concat Checklist ID and Task ID which gives me a UID for each row.
ignore the blurred out step, I don’t use it and need to remove.

 

 

 

——————————
Richard McClanahan
95628
Fair Oaks
——————————