Running Total with LASTNONBLANKVALUE

Step 1: Create the "Sales projected" measure

This measure will take the last known sales value and continue using it for future weeks.

DAX
Sales projected =
IF(
ISBLANK([Sales]),
LASTNONBLANKVALUE('Table'[Sales], [Sales]),
[Sales]
)

Explanation:
- `LASTNONBLANKVALUE('Table'[Sales], [Sales])` finds the last non-blank sales value in your table.
- `IF(ISBLANK([Sales]), ..., [Sales])` checks if the sales value is blank; if it is, it uses the last known sales value; otherwise, it uses the actual sales value.

Step 2: Create the "Running Total Sales" measure with projected sales

Now, you can create a running total that starts from the first row where `Index = 0` and includes the "Sales projected" measure for calculation.

DAX

Running Total Sales =
VAR StartingIndex =
CALCULATE(
MIN('Table'[Index]),
'Table'[Index] >= 0
)
RETURN
CALCULATE(
SUMX(
FILTER(
'Table',
'Table'[Index] >= StartingIndex &&
'Table'[Index] <= EARLIER('Table'[Index])
),
[Sales projected]
)
)

Explanation:
- `StartingIndex` finds the first row where `Index >= 0`.
- `SUMX(FILTER(...), [Sales projected])` sums up the "Sales projected" values up to the current row, only starting from the defined `StartingIndex`.

How It Works
1. The "Sales projected" measure uses the last available sales value for blank entries in the `Sales` column.

2. The "Running Total Sales" measure then calculates a cumulative total based on "Sales projected" from the point where `Index = 0`.

This approach should work effectively within your table visualization without requiring any calculated columns.

Yes, absolutely! You can achieve this entirely with DAX measures, without the need for calculated columns. Here’s how you can set up both the "Sales Projected" and "Running Total Sales Projected" as

Create the "Sales Projected" Measure

Try this measure:

Sales Projected =
VAR LastActualSales =
CALCULATE(
LASTNONBLANK('YourTable'[Sales], [Sales]),
FILTER('YourTable', 'YourTable'[Index] < 0)
)
RETURN
IF(
'YourTable'[Index] >= 0,
COALESCE(LastActualSales, 0),
[Sales]
)

This measure will populate Sales Projected with the last non-blank sales value where Index is negative (historical sales), and it will carry this value forward to rows where Index is greater than or equal to 0.

Create the "Running Total Sales Projected" Measure

Running Total Sales Projected =
VAR MaxIndex = MAX('YourTable'[Index])
RETURN
CALCULATE(
SUMX(
FILTER(
ALL('YourTable'),
'YourTable'[Index] <= MaxIndex
),
[Sales Projected]
)
)

• Sales Projected: This measure dynamically uses the last non-blank sales value for rows where Index >= 0. It does not create a separate column but instead calculates the projected sales value directly as a measure.

• Running Total Sales Projected: This measure calculates a running total by summing up Sales Projected up to the current Index. Using ALL('YourTable') ensures the measure ignores row context, so it correctly accumulates projected sales over all rows up to the current one.

When you add these two measures to your table visualization, "Sales Projected" will display the projected sales values dynamically, and "Running Total Sales Projected" will calculate the cumulative total based on those projected values, all without creating additional columns.