Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |