cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
n8ball
Regular Visitor

DATESINPERIOD from Last Date in table giving strange results

My date table contains 1/1/2017 through 1/16/2020

When I execute this:

 

Table = DATESINPERIOD ( 'Date'[Date], MAX('Date'[Date]), -1, YEAR )

 

My first date in the table is 2/1/2019. I would expect 1/17/2019. When I use -365, DAYS instead of -1, YEAR it works as expected.

 

However, when I use:

 

Table = DATESINPERIOD ( 'Date'[Date], MAX('Date'[Date])-1, -1, YEAR )

 

The first date is 1/16/2019 as I would expect.

Any idea why this is happening and what I can do to correct?

1 ACCEPTED SOLUTION
az38
Super User
Super User

Hi @n8ball 

it is difficult to describe but it is logical

for datesbetween the start day (day+month in your case) of returned table could not be equal max day (day+max) 

-1 year means that returned dataset will start from 1 year exactly +1 day

try to read this article https://radacad.com/datesinperiod-vs-datesbetween-dax-time-intelligence-for-power-bi to understand more


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

3 REPLIES 3
az38
Super User
Super User

Hi @n8ball 

it is difficult to describe but it is logical

for datesbetween the start day (day+month in your case) of returned table could not be equal max day (day+max) 

-1 year means that returned dataset will start from 1 year exactly +1 day

try to read this article https://radacad.com/datesinperiod-vs-datesbetween-dax-time-intelligence-for-power-bi to understand more


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

n8ball
Regular Visitor

Thanks I thougth something like that was happening but I couldn't find any confirmation. Time intelegence functions get funky at the ends of the date table. Do people normally just make date tables largerger than needed? Or do they use differnt DAX to achieve the result I'm looking for?

@n8ball 

usually to create calendar table CALENDAR() and CALENDARAUTO() function are used.

functions like datesinperiod() or datesbetween() are being used for filtering inside calculation. and from this point of view it has a lot of sence to avoid some duplicate. usually if you try to calculate smth for last rolling year, your calculated period will not start from the same day that end day, in common case it will be next day


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors