topic Re: undefined in DAX Commands and Tips

undefined

Anonymous — Wed, 27 Apr 2022 11:37:17 GMT

Hi ,

I have a Slicer that shows date as Between .

Here i have selected 6 days as my search period .
How can i get the selected date period as Start date and End date Dax Query ?

Re: undefined

Anonymous — Wed, 27 Apr 2022 11:43:38 GMT

i cannot achieve the expected output with FIRSTDATE , LASTDATE options .

Any Suggessions ? my expected output was start date = 3/6/2022 , end date = 3/12/2022

Please Help

Re: undefined

tamerj1 — Wed, 27 Apr 2022 12:39:03 GMT

Hi Anonymous

you can use

Start Date = MINX ( ALLSELECTED ( DateTable ), DateTable[Date] )

Start Date = CALCULATE ( MIN ( DateTable[Date] ), ALLSELECTED ( DateTable ) )

same for End Date but with MAXX or MAX

Re: undefined

Anonymous — Wed, 27 Apr 2022 13:09:26 GMT

Hi , Thanks for the reply .

But both MINX and CALCULATE ( MIN functions gives 7 not the min date of selected range . in my case the selected min date is 6th

Re: undefined

tamerj1 — Wed, 27 Apr 2022 13:25:23 GMT

Anonymous

Do you have date table? Would you please paste the code you have used?

Re: undefined

Whitewater100 — Wed, 27 Apr 2022 14:42:31 GMT

Hi:

With a date table.

Start = Calculate(MIN(Dates[Date]))

End = CALCULATE(MAX(Dates[Date]))

Re: undefined

Anonymous — Wed, 27 Apr 2022 15:32:40 GMT

Hi ,

why the answers differs ??

Re: undefined

Anonymous — Wed, 27 Apr 2022 15:41:46 GMT

Sure

Re: undefined

tamerj1 — Wed, 27 Apr 2022 16:07:04 GMT

Anonymous

Use

CALCULATE MIN = CALCULATE ( MIN ( SheetifCreatedDate]), ALLSELECTED ( Sheet1 ), REMOVEFILTERS ( ) )

Re: undefined

Whitewater100 — Wed, 27 Apr 2022 17:41:56 GMT

The date field you are using in the slicer should come from the date table. I think you don't have date table. If you use a date table you will be much happier in the future. I will paste code for a Date table. If you go to Modeling>New Table> then put the code in. Afterwhich you Mark the Date Table by selecting the Date field and validating on this. Then you connect your Date Table to your sheet one on Date Table Date to Sheet1 Date.

Then don't use the Sheet1 date field anymore. Use Date Table date field. I hope this makes sense.

Here is Date Table Code to start with:

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today()

VAR FiscalMonthEnd = 6

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Model should look like this:

Re: undefined

Anonymous — Thu, 28 Apr 2022 03:15:21 GMT

Wow , it works , thank you soo much for your kind concern 🙂

Re: undefined

Whitewater100 — Thu, 28 Apr 2022 11:41:46 GMT

Your welocme! Thank you too.