Summaries table with Earliest date / Latest date with condition

[Anonymous]

Dear Community 

I have the following issue

There are multiple projects (more than 200) . Each projects has multible discipline and each discipline has diffrent tasks in original data set. Please see sample data below. I would like to summarise data into a table and then a gant chart:  Project name, Discipline name, start date, finish date . So table should use the earliset start date as summary start date and the latest finish date as summary finish date what the earliest date and the latest date is per disicpline 

I am new in DAX and  tried to use this formulat but seems doesn't work:

eStartDate =

    CALCULATE (

        MIN ( 'Table1'[Start] ),

        FILTER (

            'Table1',

            [Start] <= EARLIER ( 'Table1'[Name] )

                               && 'Table1'[Name] = EARLIER ( 'Table1'[FY] )

        )

    )

RETURN

    IF (

        [Start] <> BLANK ()

            && [Finish] <> BLANK ()

           )

EFinishDate =

    CALCULATE (

        MAX ( 'Table1'[Finish] ),

        FILTER (

            'Table1',

            [Finish] >= EARLIER ( 'Table1'[Finish] )

                && 'Table1'[Name]= EARLIER ( 'Table1'[FY] )

        )

    )

RETURN

    IF (

        [Start] <> BLANK ()

            && [Finish] <> BLANK (),

           )

here is original data:

Project Name	Name	Discipline	Start	Finish	FY
Ramp A
Grand Road	Ramp A - Physical work Start	Ramp A	13-Nov-24
Grand Road	Ramp A - Physical work Completion	Ramp A		7-Dec-25	2026
Grand Road	Ramp A - Defect Completion	Ramp A		10-Feb-26
Grand Road	Ramp A -Operational Readyness	Ramp A		15-Apr-26
Access B
Grand Road	Access B - Physical work Start	Access B	7-Mar-23
Grand Road	Access B - Physical work Completion	Access B	7-Mar-23	28-Aug-25	2026
Grand Road	Access B- Defect Completion	Access B		16-Nov-25
Control Unit
Grand Road	Control Unit - Physical work Start	Control Unit	22-Aug-24
Grand Road	Control Unit - Physical work Completion	Control Unit	22-Aug-24	31-Mar-25	2025
Grand Road	Control Unit - Defect Completion	Control Unit	1-Nov-24	28-Apr-25	2026
Grand Road	Control Unit -Operational Readyness	Control Unit		15-Sep-25
Road Landscape
Holt Ave	Road Landscape - Physical work Start	Road Landscape	16-Jan-23
Holt Ave	Road Landscape- Physical work Completion	Road Landscape	16-Jan-23	11-Aug-23	2024
Holt Ave	Road Landscape - Defect Completion	Road Landscape		14-Feb-24
Holt Ave	Road Landscape -Operational Readyness	Road Landscape	14-Feb-24	14-Feb-24
StormWater Tank
Holt Ave	StormWater Tank  - Physical work Start	StormWater Tank	1-Mar-22
Holt Ave	StormWater Tank - Physical work Completion	StormWater Tank	31-Mar-23		2023
Holt Ave	StormWater Tank  Defect Completion	StormWater Tank		31-Dec-24
Holt Ave	StormWater Tank  -Operational Readyness	StormWater Tank		1-Mar-25

Summarised table should be like this

Project Name	Name	Start	Finish	FY
Grand Road	Ramp A	13-Nov-24	15-Apr-26	2027
Grand Road	Access B	7-Mar-23	16-Nov-25	2026
Grand Road	Control Unit	22-Aug-24	15-Sep-25	2026
Holt Ave	Road Landscape	16-Jan-23	14-Feb-24	2024
Holt Ave	StormWater Tank	1-Mar-22	1-Mar-25	2025

And summarise gant visual like this:

Can anyone help me with this?

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Name", type text}, {"Discipline", type text}, {"Start", type date}, {"Finish", type date}, {"FY", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Project Name] <> null and [Project Name] <> ""),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Name] <> null and [Name] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Project Name", "Discipline"}, {{"Start", each List.Min([Start]), type nullable datetime}, {"End", each List.Max([Finish]), type nullable datetime}})
in
    #"Grouped Rows"

I do not know how to get the FY.

[Anonymous]

Thanks I will make a try. 

for now I created two columns summary start date and summary finish date with project and discipline filter: 

SummaryFinishDate =

VAR ProjectName = 'Table1'[Project Name ]

VAR DisciplineName = 'Table1'[Discipline]

RETURN

    CALCULATE(

        MAX('Table1'[Finish].[Date]),

        FILTER(

            'Table1',

            'Table1'[Project Name ] = ProjectName &&

            'Table1'[Discipline] = Disciplinename

        )

    )

And it works for Table and linked Gantt.

[Anonymous]

Hi  v-jianpeng-msft

Thanks for your Response. I can't create a new Table as I have to add more columns/value from current data set to my visual once early start and late finish summary sortted. And also there is not a unique ID/cpde to make a relationship between data sets or Tables. May have to create  multiple columns in my current data set. Any Idea?

I am trying with this DAX :

SummaryFinishDate =

VAR ProjectName = 'Table1'[Project Name ]

VAR DisciplineName = 'Table1'[Discipline]

RETURN

    CALCULATE(

        MAX('Table1'[Finish].[Date]),

        FILTER(

            'Table1',

            'Table1'[Project Name ] = ProjectName &&

            'Table1'[Discipline] = Disciplinename

        )

    )

[Anonymous · Accepted solution]

Hi @Anonymous 

Thanks for your reply. If you can't create a new calculation table. You can create a measure as follows:

start date = 
VAR _table = CALCULATETABLE (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
        'Table'[Project Name],
        'Table'[Discipline],
        "start", CALCULATE ( MIN ( 'Table'[Start] ) ),
        "Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
        "FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
    )
)
VAR _project_name = SELECTEDVALUE('Table'[Project Name])
VAR _discipline = SELECTEDVALUE('Table'[Discipline])
RETURN CALCULATE(
    MAX([Start]),
    FILTER(_table,
        'Table'[Discipline]=_discipline&&'Table'[Project Name]=_project_name
    )
)

End date = 
VAR _table = CALCULATETABLE (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
        'Table'[Project Name],
        'Table'[Discipline],
        "start", CALCULATE ( MIN ( 'Table'[Start] ) ),
        "Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
        "FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
    )
)
VAR _project_name = SELECTEDVALUE('Table'[Project Name])
VAR _discipline = SELECTEDVALUE('Table'[Discipline])
RETURN CALCULATE(
    MAX([Finish]),
    FILTER(_table,
        'Table'[Discipline]=_discipline&&'Table'[Project Name]=_project_name
    )
)

Use these two measures in your Gantt chart:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @Anonymous 

Depending on your description, you can try the following DAX expressions:

Table 2 = 
CALCULATETABLE (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Name] <> BLANK () ),
        'Table'[Project Name],
        'Table'[Discipline],
        "start", CALCULATE ( MIN ( 'Table'[Start] ) ),
        "Finish", CALCULATE ( MAX ( 'Table'[Finish] ) ),
        "FY", VAR _maxday = MAX ( 'Table'[Finish] ) RETURN YEAR ( _maxday )
    )
)

 The summary table is as follows:

You can then use these fields in your gant plot as follows:

You can look for the Gantt chart you want from the visual marketplace:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.