Forum Discussion
summarize all rows on a given date based on their status for each row.
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.
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
1 Reply
- BA_Pete
Super User
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