Fill gaps between dates and extend latest value to max date

[ila]

Hi , I am really struggling with the following.

Below my sample data. I need to  plot something similar to the chart below where at each date I have the tot amount of documents approved and not approved

document	date	status
Doc 1	05/01/2023	Approved
Doc 1	10/01/2023	Not Approved
Doc 1	15/01/2023	Approved
Doc 2	05/01/2023	Not Approved
Doc 2	10/01/2023	Approved
Doc 2	15/01/2023	Approved
Doc 3	10/01/2023	Approved
Doc 4	25/01/2023	Approved
Doc 4	30/01/2023	Not Approved
Doc 5	05/01/2023	Approved
Doc 5	06/01/2023	Approved
Doc 6	10/01/2023	Not Approved
Doc 7	05/01/2023	Approved
Doc 7	30/01/2023	Not Approved

[tackytechtom]

Hi @ila ,

How about this:

The diagram looks a bit different than yours. I am not sure, whether you are actually looking for a running total or whether there are some data points missing in the table you provided.

Anyway, I just added a Date dimension and used the Dates on the x-Axis of a scatter chart

Why we need a Date Table in Power BI - YouTube

Then I created a NumberOfDocs measure to be used on the y-axis

NumberOfDocs =

CALCUlATE (

    COUNT ( 'Table'[document] )

)

Finally, I added the Status column to the Legend attribute:

Let me know if this solves yoru issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[ila]

Hi @tackytechtom thanks for your reply. Unfortunately this is not what I am looking for. At each specific point in time I need to know how many docs have been approved until that date (cumulative). This means that I need to fill the values gaps between dates and extend the latest value for each document until the max date value across the table. I have slightly reduced the amount opf samle data. If this is my starting point :

document	date	status
Doc 1	05/01/2023	Approved
Doc 1	10/01/2023	Not Approved
Doc 1	15/01/2023	Approved
Doc 2	05/01/2023	Not Approved
Doc 2	10/01/2023	Approved
Doc 2	15/01/2023	Approved
Doc 3	10/01/2023	Approved
Doc 4	17/01/2023	Approved

This is what I would need: 

document	date	status
Doc 1	05/01/2023	Approved
Doc 1	06/01/2023	Approved
Doc 1	07/01/2023	Approved
Doc 1	08/01/2023	Approved
Doc 1	09/01/2023	Approved
Doc 1	10/01/2023	Not Approved
Doc 1	11/01/2023	Not Approved
Doc 1	12/01/2023	Not Approved
Doc 1	13/01/2023	Not Approved
Doc 1	14/01/2023	Not Approved
Doc 1	15/01/2023	Approved
Doc 1	16/01/2023	Approved
Doc 1	17/01/2023	Approved
Doc 2	05/01/2023	Not Approved
Doc 2	06/01/2023	Not Approved
Doc 2	07/01/2023	Not Approved
Doc 2	08/01/2023	Not Approved
Doc 2	09/01/2023	Not Approved
Doc 2	10/01/2023	Approved
Doc 2	11/01/2023	Approved
Doc 2	12/01/2023	Approved
Doc 2	13/01/2023	Approved
Doc 2	14/01/2023	Approved
Doc 2	15/01/2023	Approved
Doc 2	16/01/2023	Approved
Doc 2	17/01/2023	Approved
Doc 3	10/01/2023	Approved
Doc 3	11/01/2023	Approved
Doc 3	12/01/2023	Approved
Doc 3	13/01/2023	Approved
Doc 3	14/01/2023	Approved
Doc 3	15/01/2023	Approved
Doc 3	16/01/2023	Approved
Doc 3	17/01/2023	Approved
Doc 4	17/01/2023	Approved

[tackytechtom · Accepted solution]

Hi @ila ,

Here a solution in Power Query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough). Also, you need to have a date table in place for this solution to work. In our case, the table is called Date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPVjBUUNJRMjDVNzDUNzIwMgZyHAsKivLLUlOUYnWQlBgaICnxyy9RwKEMr0lG6HZhNcgI3TrsKvDaZEzQDBOgoBFeM0AqjAl625Rg+IFVmOFTYUZUAJsTtMkcv4tjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [document = _t, date = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", type text}, {"date", type date}, {"status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"Grouping", each addDates(_), type table [document=nullable text, date=nullable date, status=nullable text]}}),
    addDates = (Table as table) as table =>
    let 
        #"Merged Queries" = Table.NestedJoin(Table, {"date"}, Date, {"date"}, "Date.1", JoinKind.RightOuter),
        #"Expanded Date.1" = Table.ExpandTableColumn(#"Merged Queries", "Date.1", {"date"}, {"Date.1.date"}),
        #"Filled Down" = Table.FillDown(#"Expanded Date.1",{"status"})
    in
        #"Filled Down",
    #"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "Grouping", {"status", "Date.1.date"}, {"status", "date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Groups", each [status] <> null and [status] <> "") 
in
    #"Filtered Rows"

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[ila]

Amazing Tom, this is perfect! I really appreciate it!