Forum Discussion
Adding two single cells together to create rolling total.
hi all, this is very easy to do in excel. I have included some example data of what i am trying to acheive. I need a rolling number for HRS where it adds from the HRS roling and HRS worked. Hope this is enough info.
Thanks you
Hey jkesavan ,
unfortunately there is nothing such like a cell in Power BI, not to mention that there is no such concept like a sequence. This is due to the fact that data is stored in tables, and even if most of the time we are referencing columns in measures and calculated columns, we have to face the challenge of unsorted rows inside these tables.
For this reason, the solution for your requirement may look a little exaggerated, but be assured it's nevertheless blazingly fast, at least most of the time.
Here is a DAX statement that I'm using to calculate a measure that I'm calling "SalesAmount cumulated":
SalesAmount cumulated = CALCULATE( SUM('FactSales'[SalesAmount]) , FILTER( ALL('DimDate'[Datekey]) , 'DimDate'[Datekey] <= MAX('DimDate'[Datekey]) ) )This is exactly doing what you are wanting, see the next screenshot:
What is essential for a working solution is a column with values that can be sorted implicitly sorted, like dates, integer, etc. This is because there is no such thing as an index that can be used for sorting.
The measure above works like this: aggregate (using the aggregation function SUM) all values from column SalesAmount across all rows that are filtered. The rows are filtered, by this filter expression (the 2nd parameter of the CALCULATE function from the DAX expression above): filter all rows where the datekey is less or equal to the current datekey. The current datekey is determined by the expression on the right hand side of the fcondition MAX(...).
As a lot of these rolling, gliding, cumulation requirements are related to time, I recommend reading this article about time related calculations: https://www.daxpatterns.com/time-patterns/
Hopefully, this provides what you are looking for.
Regards,
Tom
4 Replies
- amitchandak
Super User
jkesavan , I you have date you can do it like this . else use allselected /all on the table in place of date table
Cumm Sales = CALCULATE(SUM(Table[Hours Worked]),filter(allselected (date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Table[Hours Worked]),filter(allselected (date),date[date] <=max(Table[Sales Date])))
Cumm Sales = CALCULATE(SUM(Table[Hours Worked]),filter(allselected (date),date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))Cumm Sales = CALCULATE(SUM(Table[LTI's]),filter(allselected (date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Table[LTI's]),filter(allselected (date),date[date] <=max(Table[Sales Date])))Cumm Sales = CALCULATE(SUM(Table[LTI's]),filter(allselected (date),date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))
In place of the date you can use month in YYYYMM format
or convert current format to date
Date = "01-" & [month] //Change data type to date
- TomMartens
Hey jkesavan ,
unfortunately there is nothing such like a cell in Power BI, not to mention that there is no such concept like a sequence. This is due to the fact that data is stored in tables, and even if most of the time we are referencing columns in measures and calculated columns, we have to face the challenge of unsorted rows inside these tables.
For this reason, the solution for your requirement may look a little exaggerated, but be assured it's nevertheless blazingly fast, at least most of the time.
Here is a DAX statement that I'm using to calculate a measure that I'm calling "SalesAmount cumulated":
SalesAmount cumulated = CALCULATE( SUM('FactSales'[SalesAmount]) , FILTER( ALL('DimDate'[Datekey]) , 'DimDate'[Datekey] <= MAX('DimDate'[Datekey]) ) )This is exactly doing what you are wanting, see the next screenshot:
What is essential for a working solution is a column with values that can be sorted implicitly sorted, like dates, integer, etc. This is because there is no such thing as an index that can be used for sorting.
The measure above works like this: aggregate (using the aggregation function SUM) all values from column SalesAmount across all rows that are filtered. The rows are filtered, by this filter expression (the 2nd parameter of the CALCULATE function from the DAX expression above): filter all rows where the datekey is less or equal to the current datekey. The current datekey is determined by the expression on the right hand side of the fcondition MAX(...).
As a lot of these rolling, gliding, cumulation requirements are related to time, I recommend reading this article about time related calculations: https://www.daxpatterns.com/time-patterns/
Hopefully, this provides what you are looking for.
Regards,
Tom
- v-easonf-msft
Community Support
Hi, jkesavan
You can try measure as below if you don't consider year:
HRS Rolling = var MyMonth=MAX('Table'[Month]) return CALCULATE( SUM('Table'[HRS Worked]) ,FILTER( ALL('Table') ,'Table'[Month]<=MyMonth ) )LTI Rolling = var MyMonth=MAX('Table'[Month]) return CALCULATE( SUM('Table'[LTI's]) ,FILTER( ALL('Table') ,'Table'[Month]<=MyMonth ) )You also can try calculated column as below:
Column_HRS Worked = CALCULATE(SUM('Table'[HRS Worked]),FILTER( 'Table','Table'[Month]<=EARLIER('Table'[Month])))Column_LTI Rolling = CALCULATE(SUM('Table'[LTI's]),FILTER( 'Table','Table'[Month]<=EARLIER('Table'[Month])))The result will show as below:
Please check the attached file for more details.
Best Regards,
Community Support Team _ Eason - Ashish_Mathur
Hi,
Assuming:
- HRS worked is a measure; and
- Month in your visual had been dragged from the Calendar Table; and
- There is a relationship between the Date column of your Data Table to the Date column of your Calendar Table
try this measure
=calculate([HRS worked],datesbetween(Calendar[Date],minx(all(calendar),calendar[date]),max(calendar[date])))
Hope this helps.
