Special cumulative total

[chef123]

Hello! 

Hope someone can help me with this because I'm out of ideas. I have data which tracks status changes for all registered devices (see simple example in screenshot below). A device can either be in use or not in use and when the status changes, this is recorded in this table.

 

I want to write a measure which is a cumulative total (count) of all distinct active devices. The actual end-result I'm looking to get is a graph which shows me how many devices were active at any given point in time. 

I know how to throw out all but the most recent record per serial number (because only the latest status change matters) to get a count of all active distinct serial numbers. I also know how to create a cumulative total. I have trouble however combining these two. 

I had hoped the following would work, I'm basically trying here to use a cumulative total construct as input for the filter I need to throw out all but the most recent record per serial number:

running_count = 
CALCULATE(
    SUM(Table1[status_bool]);
    FILTER(
        FILTER(ALL(Table1);Table1[changedate] <= MAX(Table1[changedate]));
        [changedate]=CALCULATE(MAX(Table1[changedate]);ALLEXCEPT(Table1;Table1[Serial number]))
    )
)

But this gives me the wrong output (see screenshot)

As output I would expect:

01-05-2015:   1

01-06-2015:   2

01-07-2015:   2

01-08-2015:   2

01-09-2015:   1

01-10-2015:   0

01-11-2015:   1

01-12-2015:   2

I suspect the measure doesn't work because in this part

"FILTER(ALL(Table1);Table1[changedate] <= MAX(Table1[changedate]))"

I used ALL(Table1) instead of ALL(Table1[changedate]) which is what I usually would use in a cumulative total. PowerBI doesn't let me use only the [changedate] column. Possibly because the filter expression after needs the [Serial number] column. 

I also tried using two seperate filters (one for the cumulative total and one for throwing out all but the most recent record) as input for the outer calculate function but this also doesn't work. 

Really hope someone can help me with this one, would be greatly appreciated!! 

Thanks a lot in advance!

[v-piga-msft]

Hi @chef123 ,

I'm still a little confused about your desired output.

Why the output for 2019-1-9 is 1?

If it is convenient, could you describe your logic in more details so that we could give further advice?

Best  Regards,

Cherry

[chef123]

Hey @v-piga-msft ,

The records in this table are status changes. So on 1-5-2015 only device 1 (serial number 1) is "in use", on 1-6-2015 device 1 & 2 are "in use", and the next two months it's confirmed that device 3 & 4 are "not in use" and nothing happens to device 1 & 2 so we're still at 2 devices "in use".

Then on 1-9-2015 there is a status change on device 1, to "not in use". So at this point only device 2 still is "in use", so the desired output would be 1. On 1-10-2015 device 2 also changes to "not in use" so no more devices are "in use". Then on 1-11-2015 and 1-12-2015 device 3 & 4 change to "in use", respectively. Meaning that the amount of devices "in use" is 1 on 1-11-2015 and 2 on 1-12-2015. 

Hopefully this clears it up 🙂 Thank you in any case for having a look at this!