Forum Discussion
Create running cost column with two filters
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
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] )
)
5 Replies
- CahabaData
Memorable Member
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?
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
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] )
)
