Forum Discussion

rwamorim's avatar
rwamorim
Frequent Visitor
5 years ago

sum by occurrence type on several dates

I have a database below:

iddocumentsituationdate
22not attended25/02/2016 09:50
32not attended11/03/2016 10:03
42not attended31/03/2016 15:02
52not attended10/04/2016 09:50
62attended19/05/2016 14:03
72not attended14/09/2016 18:03
82not attended14/09/2016 18:03
92not attended02/06/2017 08:54
102not attended10/07/2017 08:54
112not attended10/08/2017 08:54
122not attended10/09/2017 08:54
132not attended10/10/2017 08:54
142not attended10/11/2017 08:54
152not attended10/12/2017 08:54
162not attended10/01/2018 08:54
172not attended10/02/2018 08:54
182not attended10/03/2018 08:54
192attended17/04/2018 11:33

 

I have several documents, in this example I only left one, with situations that change over time, so I need to know what the document's situation is on a certain date. for example, in February to April 2016 I was in a "not attended" situation. from April to August as "attended" and September 2016 to February 2018 "not attended" and March until today "attended", always counting on the situation of the last day of the month.

difficult right? I think there is no solution.

Need Help

1 Reply

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Power Query has the Table.Group function with Groupkind.Local which is perfect for this.

    Add a step in Applied Steps similar to this:

    Table.Group(
                    #"Changed Type"
                    , "situation"
                    , {
                          {"doc", each List.Min([document]) , Int64.Type}
                        , {"Start Date", each List.Min([date]), type date}
                        , {"End Date", each List.Max([date]), type date}
                        , {"Document Dates", each List.Count([date]), type number}
                      }
                    , GroupKind.Local)

     This isn't quite what you asked for (the status closest to the end of the month) but it's a good start.  If you really want that, get back to me and i'll help you with the algorithm