Running Total with LASTNONBLANKVALUE

[joshua1990]

Hey community!

I have a table visualization that shows me the sales per year-week:

Year-Week	Index	Sales	Sales projected	Running Total Sales
2024-01	-1	5	5
2024-02	0	6	6	6
2024-03	1		6	12
2024-04	2		6	18
2024-05	3		6	24

Here I would like to add a column "Sales projected" that will extrapolate the last sales for the next weeks where Index =0

Based on that I would like to add another column with the running total with the sames "Sales projected", starting with Index = 0.

How would you do that with a DAX Measure?

[FarhanJeelani]

Hi @joshua1990 , Please accept my reply as a solution if it helps you😊

[Anonymous]

Hi @joshua1990 ,

How is the situation now? If the problem has been solved, please accept replies you find helpful to this question as solutions. This will help others with the same question find solutions faster.

If none of the above is the solution you want, please provide more details and sample data. Also, does "index=0" occur more than once?

Best regards,

Mengmeng Li

[SachinNandanwar]

File : https://easyupload.io/4jimop

[ThxAlot · Accepted solution]

[FarhanJeelani]

Hey there!

Here’s how you can achieve this in Power BI:

1. Create the "Sales projected" Column:
- Use the last non-blank sales value to fill in projected sales for future weeks.
- You can create a calculated column like this:

DAX

Sales projected =
IF(
[Index] >= 0,
CALCULATE(
LASTNONBLANKVALUE('Table'[Sales], 'Table'[Sales]),
FILTER('Table', [Index] = 0)
),
[Sales]
)

- This will keep actual sales for past weeks and use the last available sales value as a projection for future weeks.

2. Create the "Running Total Sales" Column:
- Now, calculate the running total, starting from the week with `Index = 0` and using the projected sales value.

DAX

Running Total Sales =
CALCULATE(
SUM('Table'[Sales projected]),
FILTER(
ALL('Table'),
'Table'[Year-Week] <= EARLIER('Table'[Year-Week]) && 'Table'[Index] >= 0
)
)

- This formula will accumulate the "Sales projected" values from the week where `Index = 0` onward.

These formulas should give you the result you’re looking for: the projected sales for future weeks and a running total starting from `Index = 0`.

Please mark this as solutionif this helps! 😊. Appreciate Kudos

[joshua1990]

THanks a lot! Is this approach possible without any calculated column? Just with a DAX measure

[123abc]

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.

[FarhanJeelani]

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.

[VahidDM]

Hi @joshua1990 

Can you post sample data as text?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data (As you shared already)
3. Explanation in words of how to get from 1. to 2.(As you shared already)
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/