Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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