Forum Discussion
Cumulative Costs with Multiple Filters
Hello,
I'm trying to create a new column in Power Query that will show cumulative costs and take multiple filters into consideration. I created a mockup table to explain it better. I'm trying to create the total costs column.
The idea is simple, if campaign end year is "ongoing" and campaign type is "online" then it should cumulatively sum estimated costs. See campaign year 2018 for example, it sums Campaign 1 from 2017 (both ongoing and online 25.000) and Campaign 3 from 2018 (6.500). Because at the beginning of 2018, we have only these two costs. Whereas in 2020, it's the sum of Campaign 1 and Campaign 4 (both ongoing and online) and the 5th campaign. It should also automatically fill when there are no campaigns in one year (due to the ongoing and online campaigns from previous years).
I tried to achieve it with =SUMX and IF but because Campaign End Year is not a date type (for obvious reasons), it doesn't work.
Any ideas appreciated,
Thanks
Anonymous
Try this create the following column:Column = CALCULATE(SUM('Table'[Estimated Cost]),FILTER('Table',[Start Year]<=EARLIER('Table'[Start Year])&&[Compaign End Year]="Ongoing" && [Campaign Type]="Online"))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous
Is there some mistake in the description? Where you said "if campaign end year is "ongoing" and campaign type is "in person"", but there is only 1 row(Campain 2) matches this condition. Can you clarify, we cannot create formula without correct logic.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Thanks Anonymous for catching this. You are right! It should've been "ongoing" and "online". I also realized my mock-up data doesn't provide the results that I need. In my case, there are no campaigns in 2018, whereas there is cost associated to that year because of 2017 ongoing & online campaign (Campaign 1), I'll edit the original question.
Anonymous
Try this create the following column:Column = CALCULATE(SUM('Table'[Estimated Cost]),FILTER('Table',[Start Year]<=EARLIER('Table'[Start Year])&&[Compaign End Year]="Ongoing" && [Campaign Type]="Online"))
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.