Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
PraseelaS
Frequent Visitor

undefined

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 ? 

Question.PNG

1 ACCEPTED SOLUTION

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:

Whitewater100_0-1651081301213.png

 

View solution in original post

11 REPLIES 11
Whitewater100
Solution Sage
Solution Sage

Hi:

With a date table.

Start = Calculate(MIN(Dates[Date]))
End = CALCULATE(MAX(Dates[Date]))
Whitewater100_0-1651070536164.png

 

Hi , 

 

why the answers differs ?? 

2022-04-27T20_59_21.png

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:

Whitewater100_0-1651081301213.png

 

PraseelaS_0-1651115546226.png

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

Your welocme! Thank you too.

tamerj1
Super User
Super User

Hi @PraseelaS 

you can use

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

or

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

same for End Date but with MAXX or MAX

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 

PraseelaS_0-1651064682507.png

 

@PraseelaS 

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

Sure 

 

PraseelaS_2-1651073892414.pngPraseelaS_3-1651073965243.png

 

@PraseelaS 

Use 

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

PraseelaS
Frequent Visitor

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 

PraseelaS_0-1651059503114.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.