Create running cost column with two filters

[larsvedoy]

I'm trying to make a calculated column for running cost that sums the value in a cost column. My table has to columns that need to match; one column with a  branch number and one colomn with periods. It is the last column in the screen shot from Excel under I am trying to recreate in PowerBI.

Greateful for any help,

Lars

[CahabaData]

You will want to be sure your Period field is set to actually be a Date field type and not text.

But the big challenge is that I see the running total is reset on Jan 17 regardless of the branch IDs.  This is problematic since Jan 17 is repeating and so one needs another unique ID to refer to.

Is there an element of the Branch IDs that is unique to each year data set - I notice the first set is 2xxxxx and second set is 3xxxxxx - is this true throughout such that 2 and 3 don't repeat again?

[larsvedoy]

Thanks,

The date field is formated as a date field (sorry for the Norwegian standard). Jan 17 means January 2017.

There are 150 different branches, where the majority starts with 2. I see in my mock up data set the branch number starting with 3 changes - they are meant to be the same - so that there are only two different branches in this data set.

Lars

[CahabaData · Accepted solution]

so its good that a single branch ID is for the whole year and that your period field is a true date field type....  this is air code and I named your table: SampleData ..so you'll want to replace that

Running Cost =

CALCULATE (

  SUM ( SampleData[Cost] ),

  ALLEXCEPT ( SampleData, SampleData[Branch] ),

  SampleData[Period] <= EARLIER ( SampleData[Period] )

                     )

[larsvedoy]

Thanks, that solved it. I've tried different versions of the same, but didn't seem to get it right.

Lars

[CahabaData]

I understand Norway has one of the biggest and most active PBI User Groups - you'll want to check them out - they are listed elsewhere in the Groups area of this site.