Display cumulative sum of sales in LineChart, based on selected week

[Anonymous]

Hi All,

I am able to calculate the cumulative sum of sales based on the selected week (slicer). Therefore I am using this measure:

Total Sales (**bleep**.) = CALCULATE(SUM(Sales[SalesAmount]);DATESBETWEEN(Date[Date];DATE(YEAR(MAX(Date[Date]));1;1);MAX(Date[Date])))

This works fine as long as I want to use display the cumulative sales just on one row in a table. Now I would like to display the total sales in a linechart until the (last day of) selected week. On the x-axis I would like to display the weeknumbers. For example: weeknumber 5 is selected within the Week slicer. The line-chart should display the cumulative sum of sales until week 5.

I having a Sales table and a Date table.

Does anyone know how to define the right measure for that? 

Thanks

[v-sihou-msft]

@Anonymous

According to your description, you want to calculate the Cumulative Total from the first day of the day till the last day of selected week. Right?

In this scenario, if you have a year column in your table, you can directly limit the table context based on the Year and Week Number column. Please create a measure like below:

Cumulative Total = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year]) && 'Table'[weeknumber]<=MAX('Table'[weeknumber])))

Another approach is calculate the last day of week first, then limit dates based on the that day. Please try formula like below:

Cumulative Total 2 = 
var LastDayOfWeek = LASTNONBLANK('Table'[Date],MAX('Table'[Date]))
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]>=DATE(YEAR(LastDayOfWeek),1,1) && 'Table'[Date]<=LastDayOfWeek)) 

Both way can get correct result.

Regards,

[Anonymous]

Hi @v-sihou-msft,

Thanks for your reply.

Unfortunately your suggested measure does not work. I would like to display the line-chart cumulative until and with the selected weeknumber. When I use your measure, it only shows data from the selected week and not until the selected week:(

I think the problem is that when the user selects a week, it overrules/modifies the date filter context. Could it help if I create a new date table, that is not linked to the sales table?

[balumaran]

Hi, I am running into the same issue. Were you able to solve this issue?

Thanks 

-M

[Anonymous]

@balumaran

Unfortunately not:( If someone has the asnswer, please let me know!

[balumaran]

sure will do