DATESINPERIOD from Last Date in table giving strange results

[n8ball]

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?

[az38 · Accepted solution]

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

[n8ball]

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?

[az38]

@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