cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## DAX cumulative total including null values at the end

Hi All,
I have the measure Sales Cumulative, it calculate the sum corecctly until it reaches blank value, then it shows the first value for null values, which from week 104, I would like to see the previous value if the value is blank.
From week 104 to 121, I would like to see the value 4,723,049
This is the dax:

Sales Cumulative =
CALCULATE(
SUM( 'Consolidated Data'[Dollar Sales]),
FILTER(ALLSELECTED('Consolidated Data'),
'Consolidated Data'[Week Reference Age]<=MAX('Consolidated Data'[Week Reference Age])
)
)

and this the data:

2 ACCEPTED SOLUTIONS
Super User

Is it possible that the value in your column 'Consolidated Data'[Week Reference Age] is different that the column you are showing in Week in your table?  Your measure works for me:

You also don't need the FILTER statement, you can just do it like this.

``````Sales Cumulative =
CALCULATE (
SUM ( 'Consolidated Data'[Dollar Sales] ),
'Consolidated Data'[Week Reference Age] <= MAX ( 'Consolidated Data'[Week Reference Age] )
)``````

Super User

For that you are going to need a weeks table that is linked to your data table.

You can make on as a calculated table with this DAX

``Weeks = DISTINCT ( 'Consolidated Data'[Week Reference Age] )``

Put the Week Reference Age from the new table in your visual and modify your measure like this:

``````Sales Cumulative =
VAR _MaxDate =  MAX ( Weeks[Week Reference Age] )
RETURN
CALCULATE (
SUM ( 'Consolidated Data'[Dollar Sales] ), Weeks[Week Reference Age] <= _MaxDate
)``````

You can see the new Sales Cumulative measure correctly calcs the running total, even when a single product is selected.

3 REPLIES 3
Super User

For that you are going to need a weeks table that is linked to your data table.

You can make on as a calculated table with this DAX

``Weeks = DISTINCT ( 'Consolidated Data'[Week Reference Age] )``

Put the Week Reference Age from the new table in your visual and modify your measure like this:

``````Sales Cumulative =
VAR _MaxDate =  MAX ( Weeks[Week Reference Age] )
RETURN
CALCULATE (
SUM ( 'Consolidated Data'[Dollar Sales] ), Weeks[Week Reference Age] <= _MaxDate
)``````

You can see the new Sales Cumulative measure correctly calcs the running total, even when a single product is selected.

Helper III

I have tried the provided dax too. One thing I forgot to mention is I have slicer for the Prodcut, when I select I a particular product, it shows only the weeks tht have sales value, I would like to see the values for the consecutive weeks.
For exampl when select a product that has sales only in week 84, it shows only that week:

But what I would like to see the sales figure 4 from week 84 to week 121 for the that particular product:

Super User

Is it possible that the value in your column 'Consolidated Data'[Week Reference Age] is different that the column you are showing in Week in your table?  Your measure works for me:

You also don't need the FILTER statement, you can just do it like this.

``````Sales Cumulative =
CALCULATE (
SUM ( 'Consolidated Data'[Dollar Sales] ),
'Consolidated Data'[Week Reference Age] <= MAX ( 'Consolidated Data'[Week Reference Age] )
)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors