cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tminsquero75 Frequent Visitor
Frequent Visitor

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 IDReading DateCumulative EOHCumulative ESSColumn1
112/31/201592000.01700.0Shows a gap between months, first calculation should be 9/30/16. 8/31/16 value should be null
18/31/201694869.51811.0
19/30/201695589.51811.0
110/31/201695944.81819.0 
111/15/201696384.61820.0Shows mutiple readings in a month, should only use last day of month reading
111/30/201696558.91823.0
112/31/201697241.41824.0 
11/31/201797979.81829.0 
12/28/201798597.31830.0March does not have incremental hours or starts, Unit was off. Hours/Starts would be 0
13/31/201798597.31830.0
1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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. Smiley Happy

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
                )
            )
    )

r2.PNG

 

Regards

6 REPLIES 6
adamflath Member
Member

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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. 

tminsquero75 Frequent Visitor
Frequent Visitor

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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

Highlighted
adamflath Member
Member

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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. 

tminsquero75 Frequent Visitor
Frequent Visitor

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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?

v-ljerr-msft Super Contributor
Super Contributor

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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. Smiley Happy

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
                )
            )
    )

r2.PNG

 

Regards

tminsquero75 Frequent Visitor
Frequent Visitor

Re: Creating a Calculated Column to calculate monthly delta between cumulative totals

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