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.
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:
Criteria
The example below shows an instance where there is more than one in a month & a gap between 3 months
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 |
Solved! Go to Solution.
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.
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
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.
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.
Adam,
Thanks for the clarification. I need this calculation to be adaptable to all containers. Meaning, I have to cut and slice the data in many different forms. It will all go back to the Container and the two reading dates though. I will have several different data sources with Cumulative hours (& Starts) that I will need to create relationships between and do analysis on. That's why I have to figure out how to create the calculation between the Month & Previous Month to the container level. I'm wondering if DAX isn't the correct method for doing this. The data sources are SQL systems that will be updating consistently, otherwise i'd just pull it into and shape in Excel.
Thoughts?
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 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |