Forum Discussion

tminsquero75's avatar
tminsquero75
New Member
9 years ago
Solved

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 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

tminsquero75's avatar
tminsquero75
New Member
9 years ago

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