Running Totals using Multiple Filters

[LauraAshburn]

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.

Running Billings RBC =

CALCULATE(

     SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]),

    FILTER(

        ALLSELECTED('billing_activities'[Date]),

        ISONORAFTER('billing_activities'[Date], MAX('billing_activities'[Date]), DESC)

    )

)

The first screen shot shows that my running billing is working

Once I add my budget from the budget table (joined by date) it throws off my running billings and adding the measure for running budget is way off.The daily budget is correct. 

How do I add an additional filter after the dates filter to return the same product as in the first filter?

[amitchandak]

@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.

[LauraAshburn]

I haven't ever posted before. Where to up upload the .pbix? That way you can at lease see the measures, right?

[LauraAshburn]

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.

[LauraAshburn]

I haven't ever posted before. Where to up upload the .pbix? That way you can at lease see the measures, right?

[amitchandak]

@LauraAshburn , You can upload to one drive or dropbox and share the link

[LauraAshburn]

I saved it to my OneDrive but can't find where there is a url to use as a link.

[amitchandak]

@LauraAshburn , right click and get shareable link. or check  some options like three dots

[LauraAshburn]

I got nervous that posting the link would allow to see company information. I thought it would just show the .pbix layout and measuresbut without the data but when I opened the link, I could see the data. 

[LauraAshburn]

I pasted the link in Message 10

[aj1973]

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.

[LauraAshburn]

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.

[v-kelly-msft]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[LauraAshburn]

Hi Kelly. 

Here is my model. The inactive relationship is the BudgetSort which identifies my product group.

Here are the two tables separately that  would like to join. Billing on the left, Budget on the right.

When I add Budget to Billing, the weekend numbers don’t tie with the correct date

Here is the DAX for the measure for Running Budget ABC

[v-kelly-msft · Accepted solution]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[LauraAshburn]

The calculation worked for the filter on BudgetSort, that's great! I am trying to get the running budget, not the running billing. 

[LauraAshburn]

I got it to work!! Thanks so much!