Add a filter for product in a running total calculation

DAX DAX Calculations
1

I am trying to filter for a product in my DAX calculation for a running total. In the Budget table, I have a column for BudgetSort. I need to add something like IF BudgetSort = 5 to the calculation. I have been struggling for two days on this.

Running Budget RBC =
VAR LastBillingDate = CALCULATE( LASTDATE(Budget[Dates.Date]),ALL( Budget))
RETURN
IF(‘Budget Measures’[Total Budget] <> BLANK () ,
CALCULATE(
SUM(Budget[Budget]),
FILTER(
ALLSELECTED(Budget),
Budget[Dates.Date] <= LastBillingDate
)))

2

Hi @LASHBURN

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Also, if you provide DAX in your post, please format it using the built-in formatter.

Greg
_eDNA Forum - Format DAX

3

Demo.pbix (287.3 KB)

Hi Greg, the video for sanitizing was great. During the process, my measure for Running Budget RBC broke. Looking at it in a table visualization no longer shows the running total by day.

Ultimately, I need to use the MAX running budgets in a calculation for each product to compare against the MAX running billings to get a comp ratio for MTD.

Perhaps what I need to do is put a filter on the Total Budget measure for each product and use that in the Running Budget measure??

4

Running Budget ABC measure

I got it to work. Thanks for your time and how to sanitize data.