Running total, by Project ID column

[KH11NDR]

Hi Guys

I Need the running totals each month in a column for the Days column grouped by Project ID.

Thanks Guys

Project ID	Days	Date	Running Total Days
AA	16	01/01/2018	16
AA	20	01/02/2018	36
AA	20	01/03/2018	56
AA	20	01/04/2018	76
BB	1	01/01/2018	1
BB	1	01/02/2018	2
BB	6	01/03/2018	8
BB	7	01/04/2018	15

[Stachu]

syntax for calculated column

Running Total Days = 
VAR Project = 'Table'[Project ID]
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE(SUM('Table'[Days]),FILTER('Table','Table'[Project ID]=Project &&'Table'[Date]<=CurrentDate))

syntax for a measure - summary for a project gives the sum for the latest date for that project

Running Total Days Measure = 
VAR Project = SELECTEDVALUE('Table'[Project ID])
VAR CurrentDate = MAX('Table'[Date])
RETURN
CALCULATE(SUM('Table'[Days]),'Table'[Project ID]=Project,'Table'[Date]<=CurrentDate)

[KH11NDR]

Stachu.

None of those worked, In the measure, it gives me the same days as they are in each month, no rolling totals.

[Stachu · Accepted solution]

they do work for me - is the Table[Date] of data type Date? Other than that I cannot think of other reason for it not to work

[KH11NDR]

@Stachu

I've got it working, I've got a further filter, which divides the data by three, If I filter on it manually and divide your column y 3, I get exactly what I'm looking for.

Is there obviously a way to filter on two columns?

[Stachu]

I don't get the question.
in the syntax I posted there is filter on Table[Project ID] & Table[Date], so 2 columns are filtered with AND operator, you can add more columns in the same manner

[KH11NDR]

@Stachu 

Ignore the last question, I'ts working stop on, thanks.

[KH11NDR]

Column Date is Date Type

Ok, Maybe because in my real data model it's grouped by Product ID and another column called "ReportingDate".  

[Stachu]

Is ReportingDate coming from the same table, or from other table connected via join?
If the latter is the case then all Table[Date] syntax should be replaced with TableName[ReportingDate]

[Zubair_Muhammad]

@KH11NDR

Column =
CALCULATE (
    SUM ( [Days] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Project ID] ),
        [Date] <= EARLIER ( [Date] )
    )
)

[KH11NDR]

No that never worked, That's what I tried myself first.