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

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!

DAX

marie_joy
2 years ago
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.

10 Replies

Joe_Barry
Solution Sage
2 years ago
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
BeaBF
Super User
2 years ago
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
  Frequent Visitor
  2 years ago
  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
    Super User
    2 years ago
    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
Anonymous
Not applicable
2 years ago
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
  Super User
  2 years ago
  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
Anonymous
Not applicable
2 years ago
As per the formula, it will always bring the previous month value.
Anonymous
Not applicable
2 years ago
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
  Frequent Visitor
  2 years ago
  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.