Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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)
Stachu.
None of those worked, In the measure, it gives me the same days as they are in each month, no rolling totals.
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
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?
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
Column Date is Date Type
Ok, Maybe because in my real data model it's grouped by Product ID and another column called "ReportingDate".
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]
Column = CALCULATE ( SUM ( [Days] ), FILTER ( ALLEXCEPT ( Table1, Table1[Project ID] ), [Date] <= EARLIER ( [Date] ) ) )
No that never worked, That's what I tried myself first.