Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
Solved! Go to Solution.
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] )
)
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
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.
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
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.
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:
But what I would like to see the sales figure 4 from week 84 to week 121 for the that particular product:
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] )
)
User | Count |
---|---|
134 | |
74 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |