Running Total Displays Last Available Value Even with Missing Current Month Data

[marie_joy]

Hi Power BI Community,

I am trying to create a running total measure in DAX that ensures the last available value is displayed even if there are no values for the current month. My current formula is as follows:

RunningTotalValue = 
CALCULATE(
    SUM(net_marg_product_lines[amount]),
    FILTER(
        ALLSELECTED(month_table),
        month_table[cy_month_number] <= MAX(month_table[cy_month_number])
    ),
    FILTER(
        ALLSELECTED(net_marg_product_lines),
        net_marg_product_lines[fiscal_year] = MAX(net_marg_product_lines[fiscal_year])
    )
)

This formula works well for calculating the running total based on month number and fiscal year. However, if there are no values for the current month, it doesn't display the last available value from the previous months.

How can I modify this DAX formula to ensure that the running total shows the last available value even if there are no entries for the current month?

Thank you for your help!

[Rakesh1705]

Please send your mail id so that I can send you the power bi file. Here I am unable to attach the file. It says .pbix is not supported.

[marie_joy · Accepted solution]

Hi everyone,

Easiest answer is to change the relationships between my net_marg_product table and my month_table and to put it as uni-directional.

[Rakesh1705]

As per the formula, it will always bring the previous month value.

[Rakesh1705]

Source Data

Current Month Calculation

Running total Calculation

Please check even if there is no value for Jul-24 its showing the previous last running total

Now I change the value of Jul-24 from 0 to null for testing

Formula is still working

[BeaBF]

@Rakesh1705 ok and which value do you want if there is no value for that month? Can you upload the pbix in drive and share the link?

BBF

[BeaBF]

@marie_joy Hi! Try with these two measures:

RunningTotal =
CALCULATE(
SUM(net_marg_product_lines[amount]),
FILTER(
ALLSELECTED(month_table),
month_table[cy_month_number] <= MAX(month_table[cy_month_number])
),
FILTER(
ALLSELECTED(net_marg_product_lines),
net_marg_product_lines[fiscal_year] = MAX(net_marg_product_lines[fiscal_year])
)
)

LastAvailableValue =
VAR CurrentMonth = MAX(month_table[cy_month_number])
VAR FiscalYear = MAX(net_marg_product_lines[fiscal_year])
VAR LastMonthWithValue =
CALCULATE(
MAX(month_table[cy_month_number]),
FILTER(
ALL(net_marg_product_lines),
net_marg_product_lines[fiscal_year] = FiscalYear &&
net_marg_product_lines[amount] <> BLANK()
)
)
VAR LastValue =
CALCULATE(
SUM(net_marg_product_lines[amount]),
FILTER(
ALL(month_table),
month_table[cy_month_number] = LastMonthWithValue
),
FILTER(
ALL(net_marg_product_lines),
net_marg_product_lines[fiscal_year] = FiscalYear
)
)
RETURN
IF(
ISBLANK(SUM(net_marg_product_lines[amount])),
LastValue,
SUM(net_marg_product_lines[amount])
)

BBF

[marie_joy]

Hi BBF,

I still have blanks for the month with null values. However it did solve one of my other problem: DAX: Achieve Running Total with Multi-Level Breakd... - Microsoft Fabric Community Now I see correct values for each product and each accounts. Should I specific in your 'LastAvailableValue' that there is a breakdown by accounts type and product type for it to work?

[BeaBF]

@marie_joy Yes, but i need to see the file. Which value do you want where there is no value for that month? I send you my email in pvt.

I've pasted the above formula under your other post, so that you can accept the solution.

BBF

[marie_joy]

Thank you for your reply. I need the value from the previous month.

But I've actually find a very easy solution. If I change the relationship as uni-directional, no more problems! 

[Joe_Barry]

Hi @marie_joy 

Please refer to this Article and Video, it will help you solve your problem https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Joe