summarize all rows on a given date based on their status for each row.

[Anonymous]

Hi!

I have a tricky challenge in my hands and would be grateful for any help provided.

My datasheet is like this.

ID	Status	Start Date1	End Date2
1	Offline	2020-01-01	2020-01-25
2	Online	2020-01-02	null
3	Offline	2020-01-28	2020-02-25
4	Offline	2020-01-28	2020-05-20

I want to summarize like this:

ID	sum online at start date	sum offline at start date
1	1	3
2	2	2
3	3	1
4	3	1

So basically I want to summarize all rows on a given date based on their status for each row.

Trying to figure out a method to do this, maybe convert the date to number of days and then use range? Is there a good method for doing this?

Please tell me if you need more clarification. 

[BA_Pete · Accepted solution]

Hi @Anonymous ,

I know this is the Power Query forum, but I'd say you really want to use DAX for this.

Assuming that an 'Online' item isn't presumed to be so by exception of it not being 'Offline', then this should work:

1) Set up a calendar dimension table either in PQ (recommended) or DAX, whatever works for you.

2) Create a measure something like this:

_onlineAtDate =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
  COUNT(yourTable[ID]),
  yourTable[Status] = "Online".
  FILTER(
    yourTable,
    yourTable[Start Date1] <= __cDate
    && (yourTable[End Date2] >= __cDate
      || ISBLANK(yourTable[End Date2]))
  )
)

Put this in a visual with calendar[date] and it should do what you want.

Pete