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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rubenvw
Advocate I
Advocate I

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

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

5 REPLIES 5
v-sihou-msft
Employee
Employee

@Rubenvw

 

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.

 

 

6.PNG

 

Regards,

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?

 

 

 

 

 

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

Thanks 

-M

@balumaran

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

sure will do

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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