Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |