Next Deliverable for Multiple Projects

[bbajuscak]

Need assistance:

I have a listing of projects, each project has multiple deliverables with dates associated with each. How can I isolate the next deliverable  and associated date for each project?  This needs to allow for updates as we move forward to continually show the next deliverable. The ONE deliverable scheduled closest to the current date moving forward for each project

Any help is greatly appreciated!

Example data:

Project	Deliverable	Delivery Date
A	x	12/12/2024
A	y	12/31/2024
A	x	1/15/2025
A	d	1/30/2025
B	k	12/14/2024
B	k	12/27/2024
B	j	1/10/2025
C	r	12/18/2024
C	s	12/30/2024
C	t	1/9/2025
C	u	1/12/2025

I need to show:

Project	Deliverable	Delivery Date
A	x	12/12/2024
B	k	12/14/2024
C	r	12/18/2024

[Irwan · Accepted solution]

hello @bbajuscak 

you can do this in two ways, SUMMARIZE table and measure.

SUMMARIZE Table : 

- create a new table with following DAX

Summarize = 
SUMMARIZE(
    ADDCOLUMNS(
        'Table',
        "Min Date",
        MINX(
            FILTER(
                'Table',
                'Table'[Project]=EARLIER('Table'[Project])
            ),
            'Table'[Delivery Date]
        )
    ),
    'Table'[Project],
    [Min Date],
    "Min Deliverable",
    MINX(
        FILTER(
            'Table',
            'Table'[Delivery Date]=[Min Date]
        ),
        'Table'[Deliverable]
    )
)

Measure : 

- create a new measure with following DAX for looking the minimum date for each Project

Min Date =
MINX(
    FILTER(
        'Table',
        'Table'[Project]='Table'[Project]
    ),
    'Table'[Delivery Date]
)

- create another new measure with following DAX for looking Deliverable in that minimum date

Min Deliverable =
var _Date = [Min Date]
Return
MINX(
    FILTER(
        'Table',
        'Table'[Delivery Date]=_Date
    ),
    'Table'[Deliverable]
)

- Plot Project, Min Date (previously created measure), and Min Deliverable (previously created measure) in Table visual

Hope this will help.
Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[v-rzhou-msft · Accepted solution]

Hi @bbajuscak ,

I think you can create a measure and add it into visual level filter and set it to show items when value = 1.

Check Whether Closest Deliverable Each Project =
VAR _Closest_Deliverable_Each_Project =
    CALCULATE (
        MIN ( Data[Delivery Date] ),
        FILTER ( ALLEXCEPT ( Data, Data[Project] ), Data[Delivery Date] > TODAY () )
    )
RETURN
    IF ( MAX ( Data[Delivery Date] ) = _Closest_Deliverable_Each_Project, 1, 0 )

Result is as below.

Best Regards,
Rico Zhou

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

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Irwan · Accepted solution]

hello @bbajuscak 

you can do this in two ways, SUMMARIZE table and measure.

SUMMARIZE Table : 

- create a new table with following DAX

Summarize = 
SUMMARIZE(
    ADDCOLUMNS(
        'Table',
        "Min Date",
        MINX(
            FILTER(
                'Table',
                'Table'[Project]=EARLIER('Table'[Project])
            ),
            'Table'[Delivery Date]
        )
    ),
    'Table'[Project],
    [Min Date],
    "Min Deliverable",
    MINX(
        FILTER(
            'Table',
            'Table'[Delivery Date]=[Min Date]
        ),
        'Table'[Deliverable]
    )
)

Measure : 

- create a new measure with following DAX for looking the minimum date for each Project

Min Date =
MINX(
    FILTER(
        'Table',
        'Table'[Project]='Table'[Project]
    ),
    'Table'[Delivery Date]
)

- create another new measure with following DAX for looking Deliverable in that minimum date

Min Deliverable =
var _Date = [Min Date]
Return
MINX(
    FILTER(
        'Table',
        'Table'[Delivery Date]=_Date
    ),
    'Table'[Deliverable]
)

- Plot Project, Min Date (previously created measure), and Min Deliverable (previously created measure) in Table visual

Hope this will help.
Thank you.