Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rwamorim
Frequent Visitor

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.

1 REPLY 1
HotChilli
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.