Forum Discussion
Running Totals using Multiple Filters
I have read many articles for two days on how to get a running total and have not found the solution I need.
I'm trying to calculate the budget value from one table and filter the dates relevant to the month to date dates from another table. I merged a date table to only include current month. I used the max date from another table that is already filtered for dates thru today. The date part is working.
What is not working is the SUMX filter for the product. I want the Budget to be a running budget MTD.
I even created a new column in the billing activities table to try to join to the budget table but can't join on a custom column.
Hi LauraAshburn ,
As the relationship between "billing activities" and "Budget" is many to many,when you directly add the data to the table visual,it may duplicate the same value,the best way is to create a measure to get the value from budget you need :
_Budget = CALCULATE(SUM(Budget[Budget]),FILTER('Budget','Budget'[BudgetDate]=SELECTEDVALUE(billing_activities[Date])),'Budget'[BudgetSort]=5)And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
16 Replies
Hi LauraAshburn
In my understanding you are using different date tables for the same purpose, if so that's not the right way espacially when using Time Intelligence formulas like MTD
You need to add a calendar date to your model.
I have a calendar date in my model. I have my billing table and budget table joined by date. The second filter in my expression is working. What I'd like to do is add another filter in the second filter to filter by product. I would think it would be simular to SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]), but use the Budget Sort column in the billings table.
Hi LauraAshburn ,
There seems no error in your dax expression,if you could provide some sample data(with the key value,such as budgetsort,budget inside),I would test and find a solution.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
LauraAshburn , is in the second screen shot date is coming from date table ?
If yes then you need to use date table here in this measure
Running Billings RBC =
CALCULATE(
SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
)Else share the calculation of all meausres .
Or Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
I have two measures actually. The first works after some modifying.
Running Billings RBC =CALCULATE(SUM(billing_activities[Actual Billings RBC]),FILTER(ALLSELECTED('billing_activities'[Date]),ISONORAFTER('billing_activities'[Date], MAX('billing_activities'[Date]), DESC)))The second is giving me fits.Running Budget RBC =CALCULATE(SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]),FILTER(ALLSELECTED('billing_activities'[Date]),ISONORAFTER(billing_activities[Date], MAX('billing_activities'[Date]), DESC)))Now I am using the same table having the dates.The Billings and Budget tables are joined by date.In the Budget table , BudgetSort in an integer to identify the product. I created a column in the Billings table using a switch statement so both tables now have date and BudgetSort to join on. I don't know where in the calculation to filter for it or join them.I haven't ever posted before. Where to up upload the .pbix? That way you can at lease see the measures, right?
I haven't ever posted before. Where to up upload the .pbix? That way you can at lease see the measures, right?
