Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a database below:
id | document | situation | date |
2 | 2 | not attended | 25/02/2016 09:50 |
3 | 2 | not attended | 11/03/2016 10:03 |
4 | 2 | not attended | 31/03/2016 15:02 |
5 | 2 | not attended | 10/04/2016 09:50 |
6 | 2 | attended | 19/05/2016 14:03 |
7 | 2 | not attended | 14/09/2016 18:03 |
8 | 2 | not attended | 14/09/2016 18:03 |
9 | 2 | not attended | 02/06/2017 08:54 |
10 | 2 | not attended | 10/07/2017 08:54 |
11 | 2 | not attended | 10/08/2017 08:54 |
12 | 2 | not attended | 10/09/2017 08:54 |
13 | 2 | not attended | 10/10/2017 08:54 |
14 | 2 | not attended | 10/11/2017 08:54 |
15 | 2 | not attended | 10/12/2017 08:54 |
16 | 2 | not attended | 10/01/2018 08:54 |
17 | 2 | not attended | 10/02/2018 08:54 |
18 | 2 | not attended | 10/03/2018 08:54 |
19 | 2 | attended | 17/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.
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |