Dynamic Table with extrapolated dates

[Anonymous]

Hello,

I have been trying to wrap my head around how best to approach this problem.  I have been working on this for a couple days.

I have a table with a baselined  list of orders by subgroup. The table is updated each day to show which ones are still outstanding.  Once the order is filled, The outstanding date is frozen to the date filled. I am ultimately trying to generate a daily burndown chart by subgroup of outstanding orders from the baseline date.  I suspect that I have to generate a dynamic table.  Below is a sample of the orginal table and the anticipated dynamic table. Can someone please assist me in the best way to approach this problem?

Original table:

Order #	Outstanding as of	On Baselined list	Sub Group
1	7/19/2018	Yes	4
2	7/17/2018	Yes	2
3	7/17/2018	Yes	2
4	7/16/2018	No	1
5	7/15/2018	Yes	3
6	7/13/2018	Yes	3
7	7/10/2018	Yes	2
8	7/10/2018	No	2
9	7/10/2018	Yes	3
10	7/10/2018	Yes	2
11	7/9/2018	Yes	4
12	7/6/2018	Yes	1

Anticipated table:

Outstanding as of	Subgroup 1	Subgroup 2	Subgroup 3	Subgroup 4
7/6/2018	1	4	3	2
7/9/2018	0	4	3	2
7/10/2018	0	4	3	1
7/13/2018	0	2	2	1
7/15/2018	0	2	1	1
7/17/2018	0	2	0	1
7/19/2018	0	0	0	1

[v-yuezhe-msft]

@Anonymous,

I am not clear about the logic that you use to get the expected table from the sample data in original table. Could you please explain it by taking the first row data of Anticipated table as example ?

Regards,
Lydia

[Anonymous · Accepted solution]

Hello all,

I was able to solve my problem using a DAX calculation to create a burndown chart. 

I created a calendar and then added columns for each of the subgroup.  Then I used the DAX expression for each subgroup to count up all open orders on each given day.

Subgroup1 = COUNTROWS(FILTER(tablename,
(tablename[baseline]=1) &&
 (tablename[action] = "Orderbeingvalidate"  || 
tablename[action] = "InfulfillmentQueue") &&

(tablename[last_update] >= 'Backlog Calendar'[Date]) &&
(tablename[subgroup] = 1)

))

[Thejeswar]

Hi @Anonymous,

First of all, I think, the Anticipated table which you have provided is exactly the output of your sample data which you have given here.

That said, the below is the M-Query for getting the table that you need from the Original Data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdA9CsAwCAXguzgHEvOfS3QvIWPnDr0/VHx0SAlZRPhQ8fVOTIaK5Wa94yr9eT1SIw3TycPKbF4tbCzC8mfHLYWVEijNY0Etw8LKCsytztWf6TlQW45hJbvNTkYuy1gYueTZ5L/xAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, #"Outstanding as of" = _t, #"On Baselined list" = _t, #"Sub Group" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Outstanding as of", type date}, {"On Baselined list", type text}, {"Sub Group", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"Order #"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Outstanding as of", "On Baselined list", "Sub Group", "Index"}, {"Partition.Outstanding as of", "Partition.On Baselined list", "Partition.Sub Group", "Partition.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Partition.Sub Group] = 1 then "Subgroup 1" else if [Partition.Sub Group] = 2 then "Subgroup 2" else if [Partition.Sub Group] = 3 then "Subgroup 3" else if [Partition.Sub Group] = 4 then "Subgroup 4" else "Subgroup 5"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}})
in
    #"Changed Type1"

The Output is as follows

 The link for a similar post here

Hope this solves your issue!!!

If this solves, don't forget to Kudo the post and accept as solution!!!

[Anonymous]

Thank you for taking the time to assist me with your response, I am not sure I follow all of your M code,  however, after reviewing the output that you show, it is not exactly what I am looking for.  I am trying to see all of the orders that are outstanding on a daily basis ( a burndown chart as it were).  So I need to count all unfulfilled orders each day.  So thats why the anticipated results that I presented shows multiple entries in each row for each date.