Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Cross row vertical calculations

  • 1.  Cross row vertical calculations

    Bronze Contributor
    Posted 21 days ago
    I hope my subject gives a proper description for what I am trying to solve.

    I have a data set much like the one I posted below and I normally perform my calculations horizontally but in this instance I have a rather large data set filled with different project numbers and the same task. I need to calculate durations  based on the project number to understand how long the project life cycle was. i.e. 1/1/2019 to 3/15/2019 is 73 days. I need to perform this calculation in a DAX formula for each project in a table with maybe 7000 projects.

    Project # Phase Milestone Task Plan FCST Actual Duration
    1 Break Ground Build Foundation Procure Vendor 1/1/2019 1/1/2019 1/1/2019
    1 Break Ground Build Foundation Order Material 1/8/2019 1/8/2019 1/10/2019
    1 Break Ground Build Foundation Start Dig 2/1/2019 2/1/2019 2/15/2019
    1 Break Ground Build Foundation Install & Seal Footings 2/5/2019 2/5/2019 2/25/2019
    1 Break Ground Build Foundation Create Stem Walls 2/12/2019 2/12/2019 3/10/2019
    1 Break Ground Build Foundation Treat Walls 2/13/2019 2/13/2019 3/15/2019 73


    ------------------------------
    Allan Blain
    Project Manager
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 20 days ago
    HI Allan,

    Assuming that the fact table is not filtered by a Calendar DIM Table, you could use the following.

    Project Duration = DATEDIFF(FIRSTDATE(Project[Actual]), LASTDATE(Project[Actual]), Day)

    When you put that measure into a matrix or table with Project Phase as a row, it will give you the duration for that project.

    Hope that helps.

    ------------------------------
    Neville Howard
    Consultant
    Brisbane
    0732228400
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 20 days ago
    I think we're really close, except we'd have to use three filters so it know which project and which two task to use as the start end dates right?

    ------------------------------
    Allan Blain
    Project Manager
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 20 days ago
    If you put Phase and Milestone as rows of the Matrix visual, it would give Duration at each level.  Remember the rows and columns of a Matrix act as filters too.

    I am assuming that the Milestone column has multiple values for the same Phase, and that that is the level you need the duration for.

    ------------------------------
    Neville Howard
    Consultant
    Brisbane
    0732228400
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 20 days ago
    The dataset has over 200,000 rows and for 20,0000 project each with 10 t task that we're tracking. So I am thinking the formula would need a group by function to filter on each project and a datediff nested in a calculate function filtering on one task as the starting date and one as the ending date. I am guessing.

    ------------------------------
    Allan Blain
    Project Manager
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 19 days ago
    HI Allan,

    With the limited data sample shown, it is hard to be sure.  It sounds like you are saying that there are 4 levels (project, Phase, milestone, task) and you need the duration at the bottom level (Task).  I have to assume that the Task (eg Procure Vendor) appears again in a row further down with another date.
    If that is the case, then that formula will work within a Matrix visual, using a Hierarchy.

      Duration
    Project 80
      +Phase 40
        +Milestone 20
           +Task1 10
           +Task2 10
      +Phase2 40
        +Milestone2 20
           +Task1 10
           +Task2 10


    ------------------------------
    Neville Howard
    Consultant
    Brisbane
    0732228400
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Cross row vertical calculations

    Top Contributor
    Posted 19 days ago
    Hi Allan !

    It would be great if you try to explain how you want this duration to be calculated;

    Project has phases, milestones & tasks, so you want to calculate MIN Project Actual Date with MAX Project Actual Date

    Your business logic will help us determine correct DAX code for Duration calculation.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 19 days ago
    So I have 20,000 projects with 10 task in this data set. What I am wanting to find out is "for each project" how long did it take complete a task. Ignore all the phase and milestones. In the example below the vendor was procured on 1/1/2019 and the walls were treated on 3/15/2019 or 73 days later. The formula needs to loop through each project and assign two variables a date for the procurement and a date for the treatment then calculate the difference and move on to the next project. Normally this would be easy if the dates were horizontally aligned but since they're vertically listed it makes it a little more challenging.

    ------------------------------
    Allan Blain
    Project Manager
    ------------------------------

    Conference-PBI_200x200


  • 9.  RE: Cross row vertical calculations

    Top Contributor
    Posted 18 days ago
    Hi @Allan Blain

    Just a clarification..You are looking at the maximum and minimum date for each project or are you specifically looking for each project, the number of days between the Task 'Procure Vendor' and Task 'Treat Walls'? If this is the case, what happens if there is no 'Treat Walls' task for a particular project? Or is the logic something entirely different?​

    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Conference-PBI_200x200


  • 10.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 18 days ago
    Hi @Gopa Kumar Sivadasan,

    Thanks for contributing. I am looking for the actual dates and time duration between the two task for each project. If the task isn't complete then the value would be null.

    Much appreciated!​

    ------------------------------
    Allan Blain
    Project Manager
    ------------------------------

    Conference-PBI_200x200


  • 11.  RE: Cross row vertical calculations

    Top Contributor
    Posted 17 days ago
      |   view attached
    Hi @Allan Blain

    Please see if the below solution is what you were looking for. If not, please let us know.
    ​I assumed that you will be needing the Project start date and Project End Date to be shown against each project. Hence, the two additional measures. Since I am not aware of your data model, I have not considered your date table and the relationship this table has with it. Depending on that, the DAX may need to be adjusted.

    DAX for the measures:
    Project Start Date = 
    VAR _StartTask = "Procure Vendor"
    VAR _StartDate =
        CALCULATE (
            VALUES ( 'Project Table'[Actual] ),
            'Project Table'[Task] = _StartTask
        )
    RETURN
        _StartDate​
    Project End Date = 
    VAR _EndTask = "Treat Walls"
    VAR _EndDate =
        CALCULATE (
            VALUES ( 'Project Table'[Actual] ),
            'Project Table'[Task] = _EndTask
        )
    RETURN
        _EndDate
    Project Life Cycle = 
    DATEDIFF ( [Project Start Date], [Project End Date], DAY )​


    PFA the pbix file for your reference.



    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Attachment(s)

    Conference-PBI_200x200


  • 12.  RE: Cross row vertical calculations

    Bronze Contributor
    Posted 17 days ago
    Thanks @Gopa Kumar Sivadasan this is works! This is exactly what'd I'd been trying to figure out.

    You're much appreciated!​​

    ------------------------------
    Allan Blain
    Project Manager
    ------------------------------

    Conference-PBI_200x200