Forum Discussion

inesj's avatar
inesj
Frequent Visitor
5 years ago

Min/Max dates across multiple selected groups

Hi everyone,

 

I have a report in which the user can see orders information for certain contracts that they can select in a slicer.

I have two tables:

1. An "Orders" table, with all the orders information and, for each order, the contract ID it is assigned to.

2. A "Contracts" table, with all the contract information including its start and end date.

There is a Many-to-One relationship between these tables, based on "Contract ID".

 

In order to create a chart that shows the combined orders quantity for all selected contracts, I would like to find out the minimum start and end dates across all the selected contracts.

 

For example, if the user selects contracts A, B, and C in the slicer, I would like to see the following information:

 

Contract IDContractStart DateEnd DateMin Start DateEnd Start Date
1234A01/01/1731/12/1701/01/1730/06/19
1235B06/01/1831/12/1801/01/1730/06/19
1236C01/01/1930/06/1901/01/1730/06/19

 

I am guessing it cannot be that complex, but I have still been stuck on this for quite some time... I am mainly struggling with the fact that there is no common column to relate these values.
Any help would be greatly appreciated!

Many thanks in advance, and take care. 

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi inesj 

    Create measures: 

    Min Start Date =
CALCULATE (
    MIN ( Contracts[Contract Start Date] ),
    ALLSELECTED ( Contracts[Contract] ),
    ALL ( Contracts )
)
    End Start Date =
CALCULATE (
    MAX ( Contracts[Contract End Date] ),
    ALLSELECTED ( Contracts[Contract] ),
    ALL ( Contracts )
)

    If this doesn't work please share the pbix (with dummy data if necessary) that reproduces the issue

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    inesj , Create two measures like

     

    Min Start Date = minx(Allselected(Table), Table[Start Date])
    End Start Date = maxx(Allselected(Table), Table[End Date])

     

    If from contracts, then

    Min Start Date = minx(Allselected(contract), contract[Start Date])
    End Start Date = maxx(Allselected(contract), contract[End Date])