Forum Discussion

LauraAshburn's avatar
LauraAshburn
Helper I
5 years ago
Solved

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...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
LauraAshburn
Helper I
5 years ago

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.