Running total - based on the year of another table

[CarlBlunck]

Hi there,

I am having trouble understand why the following measure is returning the total amount correctly, but it is returning an incorrect amount per year.

Hopefully the picture gives enough info.

The Running Total Grant Amount (for trend) measure is defined as follows:

Running Total Grant Amount (for trend) =

var _ChartYear = Max('dim_MasterCalendar (expenses)'[YEAR])

VAR Result =

    CALCULATE (

        SUM ( fact_GrantsPerYear[Total Grant Amount] ),

        fact_GrantsPerYear[Year] <= _ChartYear,

        ALL (  fact_GrantsPerYear[Year] )

    )

RETURN

Result

There is no relationship between the fact_GrantsPerYear table and the dim_MasterCalendar (expenses).  Which I think might be part of the problem, but have been trying different methods to understand the issue for a few hours and can't work it out...

Appreciate any help here!

Thanks

Carl

[v-ssriganesh]

Hi @CarlBlunck,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @CarlBlunck,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh · Accepted solution]

Hi @CarlBlunck,
Thanks for sharing the details and the DAX you’ve been trying.

I’ve reproduced your scenario in Power BI Desktop and can confirm that the issue was due to how the row context is being picked up in the Running Total measure. Specifically, your original measure was always returning the grand total because MAX(DimYear[Year]) was not reacting to the Expense table context.

To resolve this, I used the following corrected measure that references Expense[Year] directly:

Running Grant (Trend) =

VAR _SelectedYear = MAX(Expense[Year])

RETURN

CALCULATE(

    SUM(Grant[Grant Amount]),

    FILTER(

        ALL(DimYear),

        DimYear[Year] <= _SelectedYear

    )

)

This ensures the measure reacts to each row in your visual that uses Expense[Year], resulting in the expected running total per year.

Here’s the expected output I observed:

For your reference, I’m attaching the .pbix file that reproduces your scenario and uses this corrected logic.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @CarlBlunck,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[danextian]

Hi @CarlBlunck 

Please try this:

Running Grant (Trend) = 
CALCULATE (
    SUM ( Grant[Grant Amuont] ),
    FILTER ( ALL ( Grant[Year] ), Grant[Year] <= MAX ( Expense[Year] ) )
)

[CarlBlunck]

Thanks @danextian - this returned the same result.  I think the problem is how the data joins together.  

Do you have any tips on how I can debug this in dax studio?

[rajendraongole1]

Hi @CarlBlunck  -  can you try the below measure to calculate the running total per year.

Running Total Grant Amount (for trend) =
VAR _ChartYear = MAX('dim_MasterCalendar (expenses)'[YEAR])
RETURN
CALCULATE(
SUM(fact_GrantsPerYear[Total Grant Amount]),
FILTER(
ALL(fact_GrantsPerYear),
fact_GrantsPerYear[Year] <= _ChartYear
)
)

Hope this works. please check

[CarlBlunck]

Thanks @rajendraongole1 @ - this returned the same result.  I think the problem is how the data joins together.  

Do you have any tips on how I can debug this in dax studio?