New user having trouble with cumulative totals.

Anonymous — Fri, 05 Jul 2019 11:13:44 GMT

Hi all,

New to Power BI and I'm having trouble creating cumulative totals.

I want to visualise the cost accociated with certain groups and sites all in one Line Chart visual.

So grouping the cost by e.g. Site, Group and run the total based on date so I have one line for each breakdown.

Here is a set of dumpy data similar to what I am working with.

Date	Agent	Group	Cost	Site
01/01/2019	Tester 1	ABC	2400	Hogwarts
02/01/2019	Tester 1	ABC	2400	Hogwarts
03/01/2019	Tester 1	ABC	2400	Hogwarts
04/01/2019	Tester 1	ABC	2400	Hogwarts
05/01/2019	Tester 1	ABC	2400	Hogwarts
06/01/2019	Tester 1	ABC	2400	Hogwarts
07/01/2019	Tester 1	ABC	2400	Hogwarts
08/01/2019	Tester 1	ABC	2400	Hogwarts
09/01/2019	Tester 1	ABC	2400	Hogwarts
10/01/2019	Tester 1	ABC	2400	Hogwarts
11/01/2019	Tester 1	ABC	2400	Hogwarts
12/01/2019	Tester 1	ABC	2400	Hogwarts
13/01/2019	Tester 1	ABC	2400	Hogwarts
14/01/2019	Tester 1	ABC	2400	Hogwarts
15/01/2019	Tester 1	ABC	2400	Hogwarts
16/01/2019	Tester 1	ABC	2400	Hogwarts
17/01/2019	Tester 1	ABC	2400	Hogwarts
18/01/2019	Tester 1	ABC	2400	Hogwarts
19/01/2019	Tester 1	ABC	2400	Hogwarts
20/01/2019	Tester 1	ABC	2400	Hogwarts
21/01/2019	Tester 1	ABC	2400	Hogwarts
22/01/2019	Tester 1	ABC	2400	Hogwarts
23/01/2019	Tester 1	ABC	2400	Hogwarts
24/01/2019	Tester 1	ABC	2400	Hogwarts
25/01/2019	Tester 1	ABC	2400	Hogwarts
01/01/2019	Tester 2	ABC	2400	Ankh-morpork
02/01/2019	Tester 2	ABC	2400	Ankh-morpork
03/01/2019	Tester 2	ABC	2400	Ankh-morpork
04/01/2019	Tester 2	ABC	2400	Ankh-morpork
05/01/2019	Tester 2	ABC	2400	Ankh-morpork
06/01/2019	Tester 2	ABC	2400	Ankh-morpork
07/01/2019	Tester 2	ABC	2400	Ankh-morpork
08/01/2019	Tester 2	ABC	2400	Ankh-morpork
09/01/2019	Tester 2	ABC	2400	Ankh-morpork
10/01/2019	Tester 2	ABC	2400	Ankh-morpork
11/01/2019	Tester 2	ABC	2400	Ankh-morpork
12/01/2019	Tester 2	ABC	2400	Ankh-morpork
13/01/2019	Tester 2	ABC	2400	Ankh-morpork
14/01/2019	Tester 2	ABC	2400	Ankh-morpork
15/01/2019	Tester 2	ABC	2400	Ankh-morpork
16/01/2019	Tester 2	ABC	2400	Ankh-morpork
17/01/2019	Tester 2	ABC	2400	Ankh-morpork
18/01/2019	Tester 2	ABC	2400	Ankh-morpork
19/01/2019	Tester 2	ABC	2400	Ankh-morpork
20/01/2019	Tester 2	ABC	2400	Ankh-morpork
21/01/2019	Tester 2	ABC	2400	Ankh-morpork
22/01/2019	Tester 2	ABC	2400	Ankh-morpork
23/01/2019	Tester 2	ABC	2400	Ankh-morpork
24/01/2019	Tester 2	ABC	2400	Ankh-morpork
25/01/2019	Tester 2	ABC	2400	Ankh-morpork
01/01/2019	Tester 3	ZYX	2300	Ankh-morpork
02/01/2019	Tester 3	ZYX	2300	Ankh-morpork
03/01/2019	Tester 3	ZYX	2300	Ankh-morpork
04/01/2019	Tester 3	ZYX	2300	Ankh-morpork
05/01/2019	Tester 3	ZYX	2300	Ankh-morpork
06/01/2019	Tester 3	ZYX	2300	Ankh-morpork
07/01/2019	Tester 3	ZYX	2300	Ankh-morpork
08/01/2019	Tester 3	ZYX	2300	Ankh-morpork
09/01/2019	Tester 3	ZYX	2300	Ankh-morpork
10/01/2019	Tester 3	ZYX	2300	Ankh-morpork
11/01/2019	Tester 3	ZYX	2300	Ankh-morpork
12/01/2019	Tester 3	ZYX	2300	Ankh-morpork
13/01/2019	Tester 3	ZYX	2300	Ankh-morpork
14/01/2019	Tester 3	ZYX	2300	Ankh-morpork
15/01/2019	Tester 3	ZYX	2300	Ankh-morpork
16/01/2019	Tester 3	ZYX	2300	Ankh-morpork
17/01/2019	Tester 3	ZYX	2300	Ankh-morpork
18/01/2019	Tester 3	ZYX	2300	Ankh-morpork
19/01/2019	Tester 3	ZYX	2300	Ankh-morpork
20/01/2019	Tester 3	ZYX	2300	Ankh-morpork
21/01/2019	Tester 3	ZYX	2300	Ankh-morpork
22/01/2019	Tester 3	ZYX	2300	Ankh-morpork
23/01/2019	Tester 3	ZYX	2300	Ankh-morpork
24/01/2019	Tester 3	ZYX	2300	Ankh-morpork
25/01/2019	Tester 3	ZYX	2300	Ankh-morpork

Any help would be appreciated :) Hope to hear back soon,

Kind regards,

DataBruh

Re: New user having trouble with cumulative totals.

Anonymous — Fri, 05 Jul 2019 13:17:32 GMT

-- First of all, you should have a proper DATE TABLE in your
-- model that links on dates to the Date column in your,
-- let's call it Costings - table. So, let's say the date
-- table is called Calendar (please find out how to create
-- a proper date table in a tabular model). Then hide the
-- column Date in your Costings table. Once you've done that,
-- you can use this measure:

[Cumulative Total] :=
var __lastVisibleDate = LASTDATE( Calendar[Date] )
RETURN
	CALCULATE(
		SUM( Costings[Cost] ),
		Calendar[Date] <= __lastVisibleDate,
		ALL( 'Calendar' )
	)

Best

Darek

topic New user having trouble with cumulative totals. in DAX Commands and Tips

New user having trouble with cumulative totals.

Re: New user having trouble with cumulative totals.