Forum Discussion
DATESINPERIOD Help for start date
Hi All,
Please help in analysis of Date DAX Dates in Period function for below 2 cases
1. if Selected Start Date is 31/10/2023 which is month end date, How many days of data will this function
DATESINPERIOD('Date Table'[_Date], [_Start_Date], -1,YEAR)) fetch?
2.if Selected Start Date is 27/11/2023 which is NOT month end date, How many days of data will this function DATESINPERIOD('Date Table'[_Date], [_Start_Date], -1,YEAR)) fetch?
I am getting the day count as below. please help. i want to basicallly check if the selected date is not MONTH END DATE , The function takes data from Next month?
For Ex: if i selected 27/11/2023, i am expecting function DATESINPERIOD('Date Table'[_Date], [_Start_Date], -1,YEAR)) should take data between 28/11/2023 to 27/11/2023 but i see it starts from 1/12/2023?
1. From 1/11/2022 to 31/10/2023 =365 days
2. From 1/12/2022 to 27/11/2023= 362 days
5 Replies
Should be 365 for both. Check you have a valid date table with no missing dates. How are you populating _Start_Date?
- Thanks for replyI am using below function to count rows for 1 year data based on slicer selection for start date. Please check the snap shot if i select 31/10/2023 , the row count is 365 and if i select 27/11/2023 its 3621 year-DataAvailabilityCount =CALCULATE (COUNTROWS ( 'Client_Returns_Data' ),DATESINPERIOD('Date Table'[_Date], [_Start_Date], -1,YEAR))
Easy to debug,
= VAR __pd = DATESINPERIOD( 'Date Table'[_Date], [_Start_Date], -1, YEAR ) RETURN MINX( __pd, 'Date Table'[_Date] ) & " | " & MAXX( __pd, 'Date Table'[_Date] )Thanks.. This was very helpful..
1.for 31.10.2023 Start date is 01.11.2023 (Expected result)
2.for 27.11.2023 start date is 01.12.2022 .( i was expecting 28.11.2022)
So function takes start date from the next month onwards..
is there a way to adjust dax formula to select start date from 28.11.2023 for 2) case?
Obviously, rewrite the measure [_Start_Date]. No more advices as there's no specific data model.
