Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Basically I'm given a running cumulative total (I'll show example below) and i need to have the individual values figured out
Here's how data look:
Week end date (date format) | Cumulative Hours | **Individual hours (what is needed to calculated i just inputed what its supposed to be obviously but isn't given to me) |
1/10/2021 | 1 | 1 |
1/17/2021 | 6 | 5 |
1/24/2021 | 8 | 2 |
1/31/2021 | 10 | 2 |
I know the formula would just be like ((1/17/2021) - (1/10/2021) = hrs for 1/17/2021 but i don't know what formulas to use to select the individual rows)
**The 3rd column is not given and needs to be calculated in powerbi (something that is super easy in excel but i have found difficult in powerbi lol)
Much appreciated
Solved! Go to Solution.
Hi @Jbell314 ,
You have 3 different ways to achieve this:
if [Index] = 0 then [Cumulative] else
[Cumulative] -
#"Added Index"[Cumulative]{[Index] -1}
The #"Added Index" part must have the name of the previous step before adding the custom column
2. Calculated Column
Individual Hours Calculated column =
Hours[Cumulative]
- CALCULATE (
SUM ( Hours[Cumulative] ),
FILTER ( ALL ( Hours ), Hours[Index] = EARLIER ( Hours[Index] ) - 1 )
)
3. Measure
Individual Hours measure =
SUM ( Hours[Cumulative] )
- CALCULATE (
SUM ( Hours[Cumulative] ),
FILTER ( ALL ( Hours ), Hours[Index] = SELECTEDVALUE ( Hours[Index] ) - 1 )
)
PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Jbell314 ,
You have 3 different ways to achieve this:
if [Index] = 0 then [Cumulative] else
[Cumulative] -
#"Added Index"[Cumulative]{[Index] -1}
The #"Added Index" part must have the name of the previous step before adding the custom column
2. Calculated Column
Individual Hours Calculated column =
Hours[Cumulative]
- CALCULATE (
SUM ( Hours[Cumulative] ),
FILTER ( ALL ( Hours ), Hours[Index] = EARLIER ( Hours[Index] ) - 1 )
)
3. Measure
Individual Hours measure =
SUM ( Hours[Cumulative] )
- CALCULATE (
SUM ( Hours[Cumulative] ),
FILTER ( ALL ( Hours ), Hours[Index] = SELECTEDVALUE ( Hours[Index] ) - 1 )
)
PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
119 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
177 | |
84 | |
70 | |
64 | |
54 |