Hi Expert, I need help with the following problem, any idea on how to solve it would be appreciated.I have a table that has an amount per day, so I made a calculation to generate an accumulated amount . Something like this.ProductMonthDATE AMOUNT Week Day Daily Accumulated 00237788Jan01/01/2023 100Sun 10000237788Jan01/02/2023 341Mon 44100237788Jan01/03/2023 728Tue 1,16900237788Jan01/04/2023 316Wed 1,48500237788Jan01/05/2023 (159)Thu 1,32600237788Jan01/06/2023 (743)Fri 58300237788Jan01/07/2023 150Sat 73300237788Jan01/08/2023 (67)Sun 66600237788Jan01/09/2023 (134)Mon 53200237788Jan01/10/2023 (69)Tue 46300237788Jan01/11/2023 (25)Wed 43800237788Jan01/12/2023 (66)Thu 37200237788Jan01/13/2023 799Fri 1,17100237788Jan01/14/2023 351Sat 1,52200237788Jan01/15/2023 170Sun 1,69200237788Jan01/16/2023 36Mon 1,72800237788Jan01/17/2023 (49)Tue 1,67900237788Jan01/18/2023 (34)Wed 1,64500237788Jan01/19/2023 (70)Thu 1,57500237788Jan01/20/2023 (62)Fri 1,51300237788Jan01/21/2023 142Sat 1,65500237788Jan01/22/2023 850Sun 2,50500237788Jan01/23/2023 (13)Mon 2,49200237788Jan01/24/2023 111Tue 2,60300237788Jan01/25/2023 (386)Wed 2,21700237788Jan01/26/2023 (57)Thu 2,16000237788Jan01/27/2023 737Fri 2,89700237788Jan01/28/2023 22Sat 2,91900237788Jan01/29/2023 179Sun 3,09800237788Jan01/30/2023 (91)Mon 3,00700237788Jan01/31/2023 13Tue 3,020 Now, the user wants a line chart, but he wants to select a day of the week (Monday, Tuesday, etc.) and when I select a week day (By example TUESDAY), the formulas are only accumulating the selected day.Let me explain, if I select Tuesday, the formula only accumulate the amounts for Tuesdays and what I need is for it to add up all the days but only graph the total for Tuesdays. By example when i select TUESDAY, the day 01/03/2023 only accumulate 728.When i need accumulate the days:01/01 : 100 (Sunday)01/02 : 341 (Monday)01/03 : 728 (Tuesday)TOTAL: 1169 (this is the correct amount to display)ProductMonthDATE AMOUNT Week Day Wrong Daily Accumulated Correct Daily Accumulated 00237788Jan01/03/2023 728Tue 728 1,16900237788Jan01/10/2023 (69)Tue 659 46300237788Jan01/17/2023 (49)Tue 610 1,67900237788Jan01/24/2023 111Tue 721 2,60300237788Jan01/31/2023 13Tue 734 3,020 Thanks in advance

Hi gomezc73 ,Thanks for rajendraongole1 reply.You can create a calculate column Daily Accumulated = VAR _currentDate = 'Table'[DATE] RETURN CALCULATE( SUM('Table'[AMOUNT]), FILTER( 'Table', 'Table'[DATE] <= _currentDate ) ) Then create a line chart Best regards,Albert He If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

gomezc73

Helper V

1 year ago

Solved

Issue with accumulated amounts

Hi Expert,

I need help with the following problem, any idea on how to solve it would be appreciated.

I have a table that has an amount per day, so I made a calculation to generate an accumulated amount . Something like this.

Product	Month	DATE	AMOUNT	Week Day	Daily Accumulated
00237788	Jan	01/01/2023	100	Sun	100
00237788	Jan	01/02/2023	341	Mon	441
00237788	Jan	01/03/2023	728	Tue	1,169
00237788	Jan	01/04/2023	316	Wed	1,485
00237788	Jan	01/05/2023	(159)	Thu	1,326
00237788	Jan	01/06/2023	(743)	Fri	583
00237788	Jan	01/07/2023	150	Sat	733
00237788	Jan	01/08/2023	(67)	Sun	666
00237788	Jan	01/09/2023	(134)	Mon	532
00237788	Jan	01/10/2023	(69)	Tue	463
00237788	Jan	01/11/2023	(25)	Wed	438
00237788	Jan	01/12/2023	(66)	Thu	372
00237788	Jan	01/13/2023	799	Fri	1,171
00237788	Jan	01/14/2023	351	Sat	1,522
00237788	Jan	01/15/2023	170	Sun	1,692
00237788	Jan	01/16/2023	36	Mon	1,728
00237788	Jan	01/17/2023	(49)	Tue	1,679
00237788	Jan	01/18/2023	(34)	Wed	1,645
00237788	Jan	01/19/2023	(70)	Thu	1,575
00237788	Jan	01/20/2023	(62)	Fri	1,513
00237788	Jan	01/21/2023	142	Sat	1,655
00237788	Jan	01/22/2023	850	Sun	2,505
00237788	Jan	01/23/2023	(13)	Mon	2,492
00237788	Jan	01/24/2023	111	Tue	2,603
00237788	Jan	01/25/2023	(386)	Wed	2,217
00237788	Jan	01/26/2023	(57)	Thu	2,160
00237788	Jan	01/27/2023	737	Fri	2,897
00237788	Jan	01/28/2023	22	Sat	2,919
00237788	Jan	01/29/2023	179	Sun	3,098
00237788	Jan	01/30/2023	(91)	Mon	3,007
00237788	Jan	01/31/2023	13	Tue	3,020

Now, the user wants a line chart, but he wants to select a day of the week (Monday, Tuesday, etc.) and when I select a week day (By example TUESDAY), the formulas are only accumulating the selected day.

Let me explain, if I select Tuesday, the formula only accumulate the amounts for Tuesdays and what I need is for it to add up all the days but only graph the total for Tuesdays.

By example when i select TUESDAY, the day 01/03/2023 only accumulate 728.

When i need accumulate the days:

01/01 : 100 (Sunday)

01/02 : 341 (Monday)

01/03 : 728 (Tuesday)

TOTAL: 1169 (this is the correct amount to display)

Product	Month	DATE	AMOUNT	Week Day	Wrong Daily Accumulated	Correct Daily Accumulated
00237788	Jan	01/03/2023	728	Tue	728	1,169
00237788	Jan	01/10/2023	(69)	Tue	659	463
00237788	Jan	01/17/2023	(49)	Tue	610	1,679
00237788	Jan	01/24/2023	111	Tue	721	2,603
00237788	Jan	01/31/2023	13	Tue	734	3,020

Thanks in advance

Need Help

Anonymous
1 year ago
Hi gomezc73 ,
Thanks for rajendraongole1 reply.
You can create a calculate column

Daily Accumulated = VAR _currentDate = 'Table'[DATE] RETURN CALCULATE( SUM('Table'[AMOUNT]), FILTER( 'Table', 'Table'[DATE] <= _currentDate ) )

Then create a line chart

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Issue with accumulated amounts.pbix44 KB

4 Replies

rajendraongole1
Super User
1 year ago
Hi gomezc73 - you need a measure in Power BI that calculates the correct daily accumulated amount while respecting the user's weekday selection.

Create a measure for the total running accumulation

Total Running Accumulated =
CALCULATE(
SUM('Table'[AMOUNT]),
FILTER(
ALL('Table'),
'Table'[DATE] <= MAX('Table'[DATE])
)
)

Create another measure to filter for the selected weekday

Correct Daily Accumulated =
IF(
MAX('Table'[Week Day]) IN VALUES('Table'[Week Day]),
CALCULATE(
SUM('Table'[AMOUNT]),
FILTER(
ALL('Table'),
'Table'[DATE] <= MAX('Table'[DATE])
)
)
)

Now, add a slicer for the Week Day column: Use the Week Day column as a slicer so users can select the desired weekday.

use a line chart:

Add the DATE column to the X-axis.
Use the Correct Daily Accumulated measure for the Y-axis. Hope this works.
- gomezc73
  Helper V
  1 year ago
  Hi, thank you for your early response. I tried your solution but i am still showing wrong amounts.
  
  Is it possible for you create a PBI and send me it?. maybe i am making something wrong.
  
  thanks in advance
Anonymous
Not applicable
1 year ago
Hi gomezc73 ,
Thanks for rajendraongole1 reply.
You can create a calculate column

Daily Accumulated = VAR _currentDate = 'Table'[DATE] RETURN CALCULATE( SUM('Table'[AMOUNT]), FILTER( 'Table', 'Table'[DATE] <= _currentDate ) )

Then create a line chart

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Issue with accumulated amounts.pbix44 KB
- gomezc73
  Helper V
  1 year ago
  It works!!. thank you very Much!!

Forum Discussion

Issue with accumulated amounts

4 Replies

Related content

Rolling accumulation

Rolling accumulation

Accumulated average

Help! Accumulated amount

Averagex issue