MIN/MAX Date Based on Column with Multiple Lines

[bmdailey]

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 examples below (*includes Current State & Desired State), I would like to introduce logic/condtion that can accomplish the Desired State in the attachment. In order to get to the desired state I need to introduce Date MIN/MAX, however I need the Date MIN/MAX to use the following logic:

MIN/MAX Start/Finish Dates by unique Department & associated unique release.

The goal is to roll up the dollar values at unique Department & unique Release and display the MIN Start Date & MAX Finish Date. Please let me know if any clarification is needed. 

Thanks

Byron

[erik_tarnvik]

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]

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 · Accepted solution]

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]

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.