Forum Discussion

bmdailey's avatar
bmdailey
Regular Visitor
9 years ago
Solved

MIN/MAX Date Based on Column with Multiple Lines

Hello, I have a Project Report that I am having to manually manipulate in order to roll up the dollar values because of multiple START & FINISH dates within a Project Release.   Based on the exampl...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi bmdailey,

    Based on your description, I assume your resource sample data is the same. Because your sample table is shown in screenshot, I can't copy, so I typed some part of it for reference. 

    sample table

    Then click "New Table" under Modeling on home page. Type the following formula and you will get the table below.

    NewTable =
SUMMARIZE (
    Test,
    Test[Department],
    Test[Release],
    "Budget", SUM ( Test[Budget] ),
    "Actual", SUM ( Test[Actual] ),
    "Variance", SUM ( Test[Variance] ),
    "Start Date", MIN ( Test[Start Date] ),
    "Finish Date", MAX ( Test[Finish Date] )
)



    Finally, you can create a table visual, select all the columns to display as follows.



    Best Regards,
    Angelia

erik_tarnvik's avatar
erik_tarnvik
Icon for Solution Specialist rankSolution Specialist
9 years ago

If I understand your challenge correctly this is actually super simple to achieve. Import your current state data. Make sure the data types come out correctly. Create a table visual and drop all your columns in the Values in desired order. Then use the drop-down menu for each of the columns in the Values bucket (see pic below)  of the visual to choose the appropriate aggregation for Budget, Actual, Variance, and the dates (MAX/MIN).

 

bmdailey's avatar
bmdailey
Regular Visitor
9 years ago

Erik, thanks for the suggestion. I believe I've tried this method and had an issue with repetitive lines (*opposed to the value roll up by Department, Release). It's been some time ago so I will give it a shot and let you what I find out.

 

Thanks again for the suggestion....results to follow

 

Byron

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

    Hi bmdailey,

    Based on your description, I assume your resource sample data is the same. Because your sample table is shown in screenshot, I can't copy, so I typed some part of it for reference. 

    sample table

    Then click "New Table" under Modeling on home page. Type the following formula and you will get the table below.

    NewTable =
SUMMARIZE (
    Test,
    Test[Department],
    Test[Release],
    "Budget", SUM ( Test[Budget] ),
    "Actual", SUM ( Test[Actual] ),
    "Variance", SUM ( Test[Variance] ),
    "Start Date", MIN ( Test[Start Date] ),
    "Finish Date", MAX ( Test[Finish Date] )
)



    Finally, you can create a table visual, select all the columns to display as follows.



    Best Regards,
    Angelia

  • erik_tarnvik's avatar
    erik_tarnvik
    Icon for Solution Specialist rankSolution Specialist
    9 years ago

    Sure thing. BTW that's what the aggregration is for, to avoid repetitive lines and instead get a sum, an average, a min or max etc. Good luck, let us know how it goes.