Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic Table with extrapolated dates

  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. T...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Thejeswar
Super User
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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.