cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chef123
Frequent Visitor

Special cumulative total

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.

 image.png

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)

 

image.png

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!

 

2 REPLIES 2
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Special cumulative total

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chef123
Frequent Visitor

Re: Special cumulative total

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!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors