Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
1 year ago
Solved

Running Total with LASTNONBLANKVALUE

Hey community!

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

Year-WeekIndexSalesSales projectedRunning Total Sales
2024-01-155 
2024-020666
2024-031 612
2024-042 618
2024-053 624

 

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?

Need Help

9 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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's avatar
      joshua1990
      Post Prodigy
      1 year ago

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

      • FarhanJeelani's avatar
        FarhanJeelani
        Super User
        1 year ago

        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's avatar
    VahidDM
    Super User
    1 year ago

    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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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