Dax:- Calculate Min & Max of Dates

[Anonymous]

In my scenario, I have two tables and theres no relashionship between these two

DateRange PowerQuery :- 

TimeRange

let
    Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,30,0)),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"TimeRange"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(convertToTable,{{"TimeRange", type time}})
in #"Changed Type

DateRange

   let
    StartDate = #date(2018,5,1),
    EndDate = #date(2018,5,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each TimeRange),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TimeRange"}, {"TimeRange"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"TimeRange", type time}})

in
    #"Changed Type1"

In Date-Range table i have created two calculated columns where it gives user selected Date-Time Range as below.

Start Date =

Start Date = 
 VAR maxDate = CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Date]))
VAR maxTime = CALCULATE(MAX('DateRange'[TimeRange]),ALLEXCEPT('DateRange','DateRange'[Date],DateRange[TimeRange]))
RETURN
    maxDate&" "&maxTime

End Date

End Date = ((DateRange[Start Date]+1)-TIME(0,0,1))

Now to filter the Date-Time column from May table between these start date and end date

I have written below measure and i put it in table visuals level filter.

Is Range = 
VAR currDate =
    MAX ( May[Date-Time] )
VAR _start =
    MAX ( DateRange[SelectedDate-Time] )
VAR _end =
    MAX ( DateRange[NextDate] )
RETURN
    IF ( currDate >= _start && currDate <= _end, "Y", "N" )

Now On Date-Time Column whatever the filtered values are coming between those start date and end date,

Im trying to calculate the Min and Max dates by below dax calculated columns

Min = CALCULATE(
	MIN('May'[Date-Time]),
		ALLEXCEPT('May','May'[EMP_ID],'May'[BEGIN_DATE])
	
)

But it giving me wrong output as below

Can anyone please help me out why im getting this, and how can i solve this.

Here is the sample data...

https://1drv.ms/x/s!AhiQ2f7YQHC-gbNaxoWm5s9GyB1WKA

Thanks in Advance

MohanV

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Min = CALCULATE(
	MIN('May'[Date-Time]),
		ALLSELECTED(May)	
)

Max = CALCULATE(
	MAX('May'[Date-Time]),
		ALLSELECTED(May)	
)

Best regards,

Yuliana Gu