Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need to find latest end date and earliest start date based on ID group and output 1 line of data

Projectst date1end date 1st date 2end date 2
P12/2/20182/2/20204/4/20205/5/2022
P13/3/20193/3/20213/3/20186/6/2020
P21/1/20193/3/20221/3/20173/5/2020
P21/2/20184/4/20233/4/20185/1/2022

 

What I want output to be is 

Projectst date 1end date 1start date 2end date 2
P12/2/20183/3/20213/3/20185/5/2022
P21/2/20184/4/20231/3/20175/1/2022

 

Help will be greatly appreciated.

 

Thanks

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi,

it depends a littel bit on what you want to do. Do you want to add a new table to the model? Or show the data in a table visual?

 

If you want to show it in a table you could just use 4 different measures:

Start 1 = MIN('ProjectTable'[st date1])
End 1 = MAX('ProjectTable'[end date 1])
Start 2 = MIN('ProjectTable'[st date2])
End 2 = MAX('ProjectTable'[end date 2])

 

If you want to get rid of the total row:

Start 1 = IF (HASONEVALUE('ProjectTable'[Project]), MIN('ProjectTable'[st date 1]))
End 1 = IF (HASONEVALUE('ProjectTable'[Project]), MAX('ProjectTable'[end date 1]))
Start 2 = IF (HASONEVALUE('ProjectTable'[Project]), MIN('ProjectTable'[st date2]))
End 2 = IF (HASONEVALUE('ProjectTable'[Project]), MAX('ProjectTable'[end date 2]))

 

To create a calculated table:

NewTable =
ADDCOLUMNS (
    VALUES ( ProjectTable[Project] ),
    "Start 1", CALCULATE ( MIN ( 'ProjectTable'[st date1] ) ),
    "End 1", CALCULATE ( MAX ( 'ProjectTable'[end date 1] ) ),
    "Start 2", CALCULATE ( MIN ( 'ProjectTable'[st date 2] ) ),
    "End 2", CALCULATE ( MAX ( 'ProjectTable'[end date 2] ) )
)

 

View solution in original post

ahmedoye
Resolver III
Resolver III

Hello @Anonymous , I have written 2 of the 4 DAX Measures required here. You can easily substitute Start Date 1 and End Date 1 with Start Date 2 and End Date 2 in the formula. See formulas below:

  1. Earliest Start Date 1 =
    CALCULATE (
        MINX (
            SUMMARIZE (
                'Table',
                'Table'[Project],
                "minimumdate"MIN ( 'Table'[st date1] )
            ),
            [minimumdate]
        )
     )
  2. Latest End Date 1 =
    CALCULATE (
        MAXX (
            SUMMARIZE (
                'Table',
                'Table'[Project],
                "maximumdate"MAX ( 'Table'[end date 1] )
            ),
            [maximumdate]
        )
    )

If this answers your question, kindly mark it as a solution.

View solution in original post

14 REPLIES 14
ahmedoye
Resolver III
Resolver III

Hello @Anonymous , I have written 2 of the 4 DAX Measures required here. You can easily substitute Start Date 1 and End Date 1 with Start Date 2 and End Date 2 in the formula. See formulas below:

  1. Earliest Start Date 1 =
    CALCULATE (
        MINX (
            SUMMARIZE (
                'Table',
                'Table'[Project],
                "minimumdate"MIN ( 'Table'[st date1] )
            ),
            [minimumdate]
        )
     )
  2. Latest End Date 1 =
    CALCULATE (
        MAXX (
            SUMMARIZE (
                'Table',
                'Table'[Project],
                "maximumdate"MAX ( 'Table'[end date 1] )
            ),
            [maximumdate]
        )
    )

If this answers your question, kindly mark it as a solution.

Anonymous
Not applicable

Hi, there is just one small thing I am not able to format it on the date, its not giving any option to change, would you be able to help?

Anonymous
Not applicable

In a table/calculated table in Power BI you can change the display format here if that's what you mean. The same for measures.

 

Skärmklipp.PNG

 

Hope it helps 🙂

Anonymous
Not applicable

Hi Thanks for your suggestions, I did apply those formulas but somehow its not bringing in the right values.

For some its picking up correctly but for most its just picking a random dates from the column.

 

Anonymous
Not applicable

Hi @Anonymous,

I tried my formulas on your example data and it gave the same result as what you wanted.

Anonymous
Not applicable

Thanks . Thats strange it does not for me.

 

PR 103-Feb-2031-May-2217-Mar-2014-Jul-22
PR 103-Feb-2031-Oct-2217-Mar-2015-Dec-22
PR 103-Feb-2031-Mar-2317-Mar-2015-May-23
PR 103-Feb-2014-Oct-2117-Mar-2030-Nov-21
PR 103-Feb-2029-Jul-2217-Mar-2013-Sep-22
PR 12020-02-0329-Jul-2217-Mar-2013-Sep-22
PR 103-Feb-2029-Jul-2217-Mar-2013-Sep-22
PR 103-Feb-2029-Jul-2217-Mar-2013-Sep-22
PR 103-Feb-2029-Jul-2217-Mar-2013-Sep-22
PR 103-Feb-2001-Mar-2217-Mar-2010-Mar-22
PR 103-Feb-2031-Mar-2317-Mar-2015-May-23
PR 103-Feb-2031-Mar-2317-Mar-2015-May-23
PR 103-Feb-2029-Jul-2217-Mar-2013-Sep-22
PR 103-Feb-202029-Jul-2217-Mar-2013-Sep-22
PR 103-Feb-2031-May-2217-Mar-2001-Nov-22
PR 103-Feb-2031-May-2217-Mar-2001-Nov-22
PR226-Jan-2218-May-2222-Feb-2214-Jun-22
PR208-Sep-2204-Jan-2319-Dec-2211-Apr-23
PR202-Sep-2229-Dec-2219-Jul-2209-Nov-22
PR214-Jul-2204-Nov-2214-Jul-2204-Nov-22
PR212-Sep-2205-Jan-2301-Sep-2228-Dec-22
PR211-May-2202-Sep-2219-Aug-2213-Dec-22
PR225-Jan-2316-May-2321-Feb-2313-Jun-23
PR206-Mar-2327-Jun-2303-Feb-2326-May-23
PR211-Jan-2302-May-2311-Jan-2302-May-23
PR213-Mar-2305-Jul-2302-Mar-2323-Jun-23
PR209-Nov-2207-Mar-2314-Sep-2209-Jan-23
PR231-Jul-2320-Nov-2317-Aug-2312-Dec-23
PR206-Sep-2302-Jan-2408-Aug-2301-Dec-23
PR212-May-2306-Sep-2312-May-2306-Sep-23
PR211-Jul-2301-Nov-2328-Jun-2320-Oct-23
Anonymous
Not applicable

How are you using the measures? In a table? Pivot table? Did you try the calculated table?

 

NewTable =
ADDCOLUMNS (
    VALUES ( ProjectTable[Project] ),
    "Start 1", CALCULATE ( MIN ( 'ProjectTable'[st date1] ) ),
    "End 1", CALCULATE ( MAX ( 'ProjectTable'[end date 1] ) ),
    "Start 2", CALCULATE ( MIN ( 'ProjectTable'[st date 2] ) ),
    "End 2", CALCULATE ( MAX ( 'ProjectTable'[end date 2] ) )
)
Anonymous
Not applicable

Yes, I did , with same results unfortunately.

@Anonymous Please share the formulas you have written for a check.

Anonymous
Not applicable

BL Earliest start Date =
CALCULATE (
    MINX (
        SUMMARIZE (
            Analysis,
            Analysis[Package Number],
            "minimumdate", MIN ( Analysis[SWP Baseline Start (P6)] )
        ),
        [minimumdate]
    )
)
 
BL Latest End Date =
CALCULATE (
    MAXX (
        SUMMARIZE (
            Analysis,
            Analysis[Package Number],
            "maximumdate", MAX ( Analysis[(*) Finish - P6] )
        ),
        [maximumdate]
    )
)
 

@Anonymous this looks very correct to me. Have you checked that you don't have some other report filters hindering this? Do you mind a call via Zoom and you share your screen?

Anonymous
Not applicable

I dont mind at all , I dont ahve an account on zoom though.

Anonymous
Not applicable

Ok I opened a zoom account .

Anonymous
Not applicable

Hi,

it depends a littel bit on what you want to do. Do you want to add a new table to the model? Or show the data in a table visual?

 

If you want to show it in a table you could just use 4 different measures:

Start 1 = MIN('ProjectTable'[st date1])
End 1 = MAX('ProjectTable'[end date 1])
Start 2 = MIN('ProjectTable'[st date2])
End 2 = MAX('ProjectTable'[end date 2])

 

If you want to get rid of the total row:

Start 1 = IF (HASONEVALUE('ProjectTable'[Project]), MIN('ProjectTable'[st date 1]))
End 1 = IF (HASONEVALUE('ProjectTable'[Project]), MAX('ProjectTable'[end date 1]))
Start 2 = IF (HASONEVALUE('ProjectTable'[Project]), MIN('ProjectTable'[st date2]))
End 2 = IF (HASONEVALUE('ProjectTable'[Project]), MAX('ProjectTable'[end date 2]))

 

To create a calculated table:

NewTable =
ADDCOLUMNS (
    VALUES ( ProjectTable[Project] ),
    "Start 1", CALCULATE ( MIN ( 'ProjectTable'[st date1] ) ),
    "End 1", CALCULATE ( MAX ( 'ProjectTable'[end date 1] ) ),
    "Start 2", CALCULATE ( MIN ( 'ProjectTable'[st date 2] ) ),
    "End 2", CALCULATE ( MAX ( 'ProjectTable'[end date 2] ) )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors