How do i create a calculated column or measure for totalling up another column as cumulative?

[KH11NDR]

Hi Guys,

This is what I want to do, create another column or measure that calculates Total sales per month per project.  My data is exactly how it is in the table without the "Total Sales" column i'm asking help for.

Thanks

Project	Date	Sales	Total Sales
A	01/01/2018	10	10
A	01/02/2018	20	30
A	01/03/2018	30	60
A	01/04/2018	40	100
A	01/05/2018	50	150
B	01/01/2018	5	5
B	01/02/2018	10	15
B	01/03/2018	15	30
B	01/04/2018	20	50
B	01/05/2018	30	80

[KH11NDR]

What's the Dax for this as a measure or column as I can't get the Quick measure calculation to do what I want.

[Anonymous]

@KH11NDR,

Create a measure using DAX below.

total sales = CALCULATE(SUM(Table[Sales]),FILTER(ALL(Table[Date]),Table[Date]<=MAX(Table[Date])))

Regards,
Lydia

[KH11NDR]

Sorry, That hasn't worked for me.

[Anonymous]

@KH11NDR,

What error message or what result do you get? Do you create a measure? Make sure you create a measure rather than a calculated column.

Regards,
Lydia

[KH11NDR]

Yes it's a measure, I don't get any error messages, just a blank row, column.

[Anonymous]

@KH11NDR，

Do you create table visual as mine? Could you please share PBIX file to me? You can share PBIX file through OneDrive.

Regards,
Lydia

[KH11NDR]

Do you think it has anything to do with me Unpivoting my columns?

Original Data

Project ID	01/07/2017	01/08/2017	01/09/2017	01/10/2017	01/11/2017	01/12/2017	01/01/2018	01/02/2018
AAAA	0	5	10	15	20	25	30	35
BBBB	0	7	12	18	24	30	36	42

Power BI Table

ProjectID	Dates	Value
AAAA	01/07/2017	0
AAAA	01/08/2017	5
AAAA	01/09/2017	10
AAAA	01/10/2017	15
AAAA	01/11/2017	20
BBBB	01/07/2017	0
BBBB	01/08/2017	7
BBBB	01/09/2017	12
BBBB	01/10/2017	18
BBBB	01/11/2017	24

And then in Power BI Matrix table, another row showing the cumualtive totals.

[Anonymous · Accepted solution]

@KH11NDR，

Please check attached PBIX file.

Regards,
Lydia

[KH11NDR]

Thank you, I'll use that as my suggestion, I know where I was going wrong now, I was using my Master Calendar Months for the column and not the date field from my table.

Thanks again, I'm pretty sure I'll be needing more help, Im an X QlikView 

[shebr]

HI @KH11NDR

Have you tried using the quick measures to try and get your result? Right click on your dataset and select 'Quick Measure'. Select your value and any other options and this may give you what you want.

Let us know how you get on.

Thanks

shebr

[KH11NDR]

How would you do it? As it's not working for me.

[KH11NDR]

I was just trying that, 

I selected Running total calculation, Base value as Sales and Field as Project, I was hoping that would have worked, but It's giving the same figures in the month rather than do a runninig total as expected.