topic Re: DAX to get MIN & MAX Date for a period- which doesn't change based on filters other than Date Slicer in DAX Commands and Tips

DAX to get MIN & MAX Date for a period- which doesn't change based on filters other than Date Slicer

Anonymous — Wed, 06 Jan 2021 15:32:10 GMT

Hello,

Happy New Year to All..!

I am trying to build a DAX Measure which can give the MIN & MAX Date for the selected period - for every row of the transaction table. Meaning If I select period Sept. to Nov. 2019 - the MIN Date for all the transactions falling in that period should be Sept. 01st, 2019 & the MAX date should be Nov. 30th, 2019.

I tried this formula, which works only till I do not select any other filter, but as soon as any new filter / slicer is selected, there are different values for each row.

SelectedPeriod_StartDate =

CALCULATE(

MIN(Table1[Bill Date]),

ALLSELECTED(Table1)

)

SelectedPeriod_EndDate =

CALCULATE(

MAX(Table1[Bill Date]),

ALLSELECTED(Table1)

)

Please help as to which DAX can help me to freeze the MIN & MAX Date based on the date selection, without getting affected by any other filters.

Thanks in advance.

Re: DAX to get MIN & MAX Date for a period- which doesn't change based on filters other than Date Slicer

amitchandak — Wed, 06 Jan 2021 16:00:05 GMT

Anonymous , for that better, create date table join with the date of Table1.

Try measure like

minx(allselected('Date'), 'Date'[Date])
maxx(allselected('Date'), 'Date'[Date])

Re: DAX to get MIN & MAX Date for a period- which doesn't change based on filters other than Date Slicer

Anonymous — Wed, 06 Jan 2021 16:31:20 GMT

Hello amitchandak ,

Thanks for your prompt reply.

I did as you mentioned, but when I used it in my required calculation - it is not yielding any output.

I am trying:

Sales_SelectedPeriod =

VAR DT1 = MINX(

ALLSELECTED(DimDate),

DimDate[Date]

)

VAR DT2 = MAXX(

ALLSELECTED(DimDate),

DimDate[Date]

)

RETURN

CALCULATE(

SUM(Table1[NetValue]),

FILTER(

ALL(Table 1),

AND(

Table 1[Key] = SELECTEDVALUE(Table1[Key]),

Table 1[Bill Date]

DATESBETWEEN(DimDate[Date], DT1, DT2)

)

I am trying to get the Sales based on a Key (Combination of Customer & Material) for the selected period.

Can you suggest what am I missing?

Thanks,

Re: DAX to get MIN & MAX Date for a period- which doesn't change based on filters other than Date Slicer

amitchandak — Thu, 07 Jan 2021 04:45:23 GMT

Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.