Forum Discussion
Creating a Calculated Column to calculate monthly delta between cumulative totals
Good morning,
I'm somewhat new to Power BI and am self taught. I do not have any programming or development experience as of yet.
I need to create what I believe to be a calculated column in Power BI desktop.
Fields Included:
- Hours (Number field, Cumulative Hours)
- Reading Date (Always the last day of the month, sometimes there may be multiple readings in a month)
- Container ID (We record the total cumulative hours for each Container on a monthly basis)
Criteria
- Difference of Cumulative Hours between to sequential months:
- Example "Feb Hours = 98,597.3" - "Jan Hours = 97,979.8" = Monthly Hours =617.45
- Container ID: 1
- Reading Date 2/28/17
- Hours: 98,597.3
The example below shows an instance where there is more than one in a month & a gap between 3 months
- Gap in first 3 values, Should ignore first value since they are not consecutive. Calculation should start at 3rd reading
- Multiple readings per month, should only calculate based on last reading of month and ignore other readings
- Cumulative hours do not increase, unit was not running, value would be 0
Any help on this would be greatly appreciated, I think i gave enough information to be able to help.
| Container ID | Reading Date | Cumulative EOH | Cumulative ESS | Column1 |
| 1 | 12/31/2015 | 92000.0 | 1700.0 | Shows a gap between months, first calculation should be 9/30/16. 8/31/16 value should be null |
| 1 | 8/31/2016 | 94869.5 | 1811.0 | |
| 1 | 9/30/2016 | 95589.5 | 1811.0 | |
| 1 | 10/31/2016 | 95944.8 | 1819.0 | |
| 1 | 11/15/2016 | 96384.6 | 1820.0 | Shows mutiple readings in a month, should only use last day of month reading |
| 1 | 11/30/2016 | 96558.9 | 1823.0 | |
| 1 | 12/31/2016 | 97241.4 | 1824.0 | |
| 1 | 1/31/2017 | 97979.8 | 1829.0 | |
| 1 | 2/28/2017 | 98597.3 | 1830.0 | March does not have incremental hours or starts, Unit was off. Hours/Starts would be 0 |
| 1 | 3/31/2017 | 98597.3 | 1830.0 |
Hi tminsquero75,
If I understand you correctly and totally, you should be able to use the formula below to create a calculate column to calculate the Difference of Cumulative Hours between to sequential months in your scenario. :smileyhappy:
Column = VAR lastDateOfPreviousMonth = LASTNONBLANK ( PREVIOUSMONTH ( Table1[Reading Date] ), 1 ) RETURN IF ( ISBLANK ( lastDateOfPreviousMonth ), BLANK (), Table1[Cumulative EOH] - CALCULATE ( MAX ( Table1[Cumulative EOH] ), FILTER ( ALL ( Table1 ), Table1[Container ID] = EARLIER ( Table1[Container ID] ) && Table1[Reading Date] = lastDateOfPreviousMonth ) ) )Regards
6 Replies
Hi tminsquero75,
If I understand you correctly and totally, you should be able to use the formula below to create a calculate column to calculate the Difference of Cumulative Hours between to sequential months in your scenario. :smileyhappy:
Column = VAR lastDateOfPreviousMonth = LASTNONBLANK ( PREVIOUSMONTH ( Table1[Reading Date] ), 1 ) RETURN IF ( ISBLANK ( lastDateOfPreviousMonth ), BLANK (), Table1[Cumulative EOH] - CALCULATE ( MAX ( Table1[Cumulative EOH] ), FILTER ( ALL ( Table1 ), Table1[Container ID] = EARLIER ( Table1[Container ID] ) && Table1[Reading Date] = lastDateOfPreviousMonth ) ) )Regards
Thank you for solving this riddle for me! I'm really looking forward to furher learning about Power BI.
Thanks to everyone who also took a shot at helping!
Tony
In order to accomplish this, you need to manually filter out(through page or report filters) the reading date. Or depending on the multiple readings per month, you could delete out all dates in PowerQuery except the dates that end with a 30 or 31 day date.
Then you can start using Time Intelligent functions to get your different between date:
https://msdn.microsoft.com/en-us/library/ee634763.aspx
In this case, you would have this formula:
Difference of Cumulative Hours = Sum(Cumulative Hours) - CALCULATE(sum(Cumulative Hours), PREVIOUSMONTH(Reading Date))
Note that in order for this formula to work, you need to have the Reading Date shown in your table.
Thanks for such a quick reply. I got that started, however I need it to only filter out on the same Container ID (Same engine). I tried to use an IF statement before. That didn't work.
In essence, I will have 200+ container ID's in this table and hundreds of readings per Container ID. I only want the returned value to be calculated for the same Container ID's.
Tony
Well the way DAX / PowerBi works is that the table you are making with the included calculated field, will only show the Container ID that you filter for.
So there are a couple options you can do:
1. Add a Container ID Filter on the page so you can select which Container ID to show on the Table
2. If you know that there are 200 container IDs, and you only care about analzying one of them, you should filter out all container ID's in the Power Query window (where you load the data)
3. If you know that this one particular Container ID if very important and you want to make a calculated field that is specific to Container ID #1, you would add this to the formula:
Difference of Cumulative Hours for Container ID#1 = Calculate(Sum(Cumulative Hours) - CALCULATE(sum(Cumulative Hours), PREVIOUSMONTH(Reading Date)), Container ID = #1).
The CALCULATE statement is the easy equivalent of the IF statement that you are trying to use.
