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 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
Solved! Go to Solution.
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
Hi,
Assuming:
try this measure
=calculate([HRS worked],datesbetween(Calendar[Date],minx(all(calendar),calendar[date]),max(calendar[date])))
Hope this helps.
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
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
@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
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |