how to get max date between 2 dates variables

[Shinigaloo]

Hello all,

i have a measure a bar chart in which i measure number of equipment per category and user and last occurrence of model

bar chart worked and the measure  very well , here's the measure

measure = Sum(Aggr(If([Date] = Max(TOTAL <[Category], User> Date), Count(Model)), [Date], Category, User))

Now my [Date ] vary depending on the selected period  , it should be [Date]=< vdatefinformat and

[Date] >=vdatedebformat

how can i merge this condition into my measure , so that i get the right result

Please can anyone help me ?

[edwin · Accepted solution]

assuming the dates are in the correct format just add to your set analysis:

Date={">=$(vdatedebformat)<=$(vdatefinformat )"}

i would say in all your calculations :
Sum(Date={">=$(vdatedebformat)<=$(vdatefinformat )"}Aggr(If([Date] = Max(TOTAL <[Category], User> Date={">=$(vdatedebformat)<=$(vdatefinformat )"}Date), Count( Date={">=$(vdatedebformat)<=$(vdatefinformat )"} Model)), [Date], Category, User))

obviously you need to test each of the aggregation.

see attached Set Analysis reference i found here but cant find teh actual thread anymore

[Stewarttt · Accepted solution]

Supposing you have a table as below screenshot shown, and you need to find  array formulas can help you to find max or min value between two dates in Excel. Finding max value between date 2016/7/1 and 2016/12/1: 2. Enter the below  and B5:B17 is the value range contains the max and min values you will find.

[Shinigaloo]

Thanks you edwin , it' worked

[richardchin]

Instead of giving a date such as '1/1/1973' we can give blank string '' which should get cast to the earliest date as per date type used.. e.g. CAST('' as date) Mypascoconnect retruns "1900-01-01", CAST('' as datetime) returns "1900-01-01 00:00:00.000" and CAST('' as datetime2) returns "1900-01-01 00:00:00.0000000". This works on SQL Server 2019.