Forum Discussion
Create running cost column with two filters
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] )
)
Memorable MemberYou 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] )
)
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.