Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
sabd80
Helper III
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:

2024-05-03_09-30-39.png
2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

@sabd80 

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:

jdbuchanan71_0-1714696328523.png

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] )
)

 

View solution in original post

jdbuchanan71
Super User
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] )

Then link it to your data table in the model

jdbuchanan71_0-1714701561807.png

 

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
)

jdbuchanan71_1-1714701681596.png

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

 

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
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] )

Then link it to your data table in the model

jdbuchanan71_0-1714701561807.png

 

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
)

jdbuchanan71_1-1714701681596.png

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

 

sabd80
Helper III
Helper III

Thanks for the quick reply.
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:
2024-05-03_10-53-37.png

 

But what I would like to see the sales figure 4 from week 84 to week 121 for the that particular product2024-05-03_10-58-00.png:


jdbuchanan71
Super User
Super User

@sabd80 

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:

jdbuchanan71_0-1714696328523.png

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] )
)

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.