Help Request - Track Line Status Changes by Date

[jwesle]

Hello,

I'm new to PowerBI and struggling to create code to track productivity. Below is an example of the data I am working with:

Name	ProductKey	Report Date	Note
Jim	Silver1	11/14/2018	0
Jim	Silver1	11/15/2018	0
Jim	Silver1	11/16/2018	1
Jim	Silver1	11/17/2018	1
Jim	Silver2	11/15/2018	0
Jim	Silver2	11/16/2018	1
Jim	Gold1	11/14/2018	1
Jim	Gold1	11/15/2018	1
Jim	Gold1	11/16/2018	1
Jim	Gold2	11/15/2018	1
Jane	Bronze1	11/15/2018	1
Jane	Bronze1	11/16/2018	1
Jane	Bronze2	11/14/2018	0
Jane	Bronze2	11/15/2018	1
Jane	Bronze2	11/16/2018	1
Jane	Tin1	11/15/2018	0
Jane	Tin1	11/16/2018	1
Jane	Tin2	11/16/2018	0

Here's the situation: We are trying to calculate the number of times an individual changes the status of a line each day. When an individual completes a task, the "Note" column will change from 0 to 1. The line may be on the report for several days before and/or after the task is completed, which makes it difficult to count when the work is actually completed. For the above example, the ideal output would be the table below:

	11/14/2018	11/15/2018	11/16/2018	11/17/2018
Jim	1	1	2	0
Jane	0	2	1	0

Any thoughts?  Thanks!

[Anonymous · Accepted solution]

Hi @jwesle,

You can try to use following calculated column formula to replace note column, then drag this one to matrix visual value field with summary mode 'sum'.

Replaced = 
IF (
    COUNTROWS (
        FILTER (
            Table,
            [Name] = EARLIER ( [Name] )
                && [ProductKey] = EARLIER ( Table[ProductKey] )
                && [Report Date] < EARLIER ( Table[Report Date] )
                && [Note] = 1
        )
    )
        > 0,
    0,
    [Note]
)+0

Regards,

Xiaoxin Sheng

[Greg_Deckler]

I would add a column and use EARLIER to return a zero or 1 for whether an item was completed on a particular row. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...