topic Min/Max dates across multiple selected groups in DAX Commands and Tips

Min/Max dates across multiple selected groups

inesj — Sat, 26 Dec 2020 10:52:50 GMT

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 ID	Contract	Start Date	End Date	Min Start Date	End Start Date
1234	A	01/01/17	31/12/17	01/01/17	30/06/19
1235	B	06/01/18	31/12/18	01/01/17	30/06/19
1236	C	01/01/19	30/06/19	01/01/17	30/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.

Re: Min/Max dates across multiple selected groups

AlB — Sat, 26 Dec 2020 14:27:30 GMT

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

Re: Min/Max dates across multiple selected groups

amitchandak — Mon, 28 Dec 2020 11:34:03 GMT

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])