topic Re: Running Total Displays Last Available Value Even with Missing Current Month Data in DAX Commands and Tips

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

marie_joy — Mon, 22 Jul 2024 09:25:54 GMT

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!

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

Joe_Barry — Mon, 22 Jul 2024 09:29:54 GMT

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

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

BeaBF — Mon, 22 Jul 2024 09:56:30 GMT

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

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

Anonymous — Mon, 22 Jul 2024 10:19:01 GMT

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

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

BeaBF — Mon, 22 Jul 2024 10:56:07 GMT

Anonymous 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

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

marie_joy — Mon, 22 Jul 2024 11:04:41 GMT

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?

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

Anonymous — Mon, 22 Jul 2024 11:18:07 GMT

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

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

Anonymous — Mon, 22 Jul 2024 11:27:15 GMT

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.

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

marie_joy — Mon, 22 Jul 2024 12:06:41 GMT

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.

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

BeaBF — Mon, 22 Jul 2024 12:41:52 GMT

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

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

marie_joy — Mon, 22 Jul 2024 12:45:02 GMT

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!