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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
tminsquero75
New Member
9 years ago

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?