topic Re: Last date in period issue when use DatesBetween in DAX Commands and Tips

Last date in period issue when use DatesBetween

QQB — Fri, 12 Jul 2024 16:05:24 GMT

Hello,

It's the first time I am posting a question here. Hopefully I do it right. I have a question with Last date issue when use DATESBETWEEN function hope someone can help with.

I have a parameter table with parameter of 1-12

I also have a DimDate table with a InvoiceDate column and End of Month column.

What I am trying to achieve is to calculate sales based on period. If the parameter is 2, then I want to evaluate sales every 2 month as a period. For example, If the [End of Month] is 02/28/2022, then current period is 01/01/2022-02/28/2022, last period should be 11/01/2021-12/31/2021. However when I use the DATESBETWEEN function to get the last period, the last date is not showing as I would like because the last date in each month is differen: the last date in the last period is 12/28/2021, instead of 12/31/2021.

How can I get the correct last date for the last period? Thank you!

First Date DatesBetween =

FIRSTDATE(

DATESBETWEEN(

DimDate[InvoiceDate],

DATEADD(FIRSTDATE(DimDate[InvoiceDate]),-(2*[Parameter Value]-1),MONTH)

,DATEADD(LASTDATE(DimDate[InvoiceDate]),-1*[Parameter Value],MONTH)))

Last Date DatesBetween =

LASTDATE(

DATESBETWEEN(

DimDate[InvoiceDate],

DATEADD(FIRSTDATE(DimDate[InvoiceDate]),-(2*[Parameter Value]-1),MONTH)

,DATEADD(LASTDATE(DimDate[InvoiceDate]),-1*[Parameter Value],MONTH)))

Re: Last date in period issue when use DatesBetween

MattAllington — Sat, 13 Jul 2024 06:25:16 GMT

I suggest you write some custom time Intelligence. I talk about it here

https://exceleratorbi.com.au/dax-time-intelligence-beginners/

and here (additional features of a good calendar table)

https://exceleratorbi.com.au/power-bi-calendar-tables/

once you have a monthID column in your calendar table, use that to do your calendar time shift. All you need to do in the filter portion is filter the calendar table, you don't need all that extra datesbetween stuff.

FILTER(ALL(Calendar),Calendar[MonthID] >= MIN(Calendar[MonthID])-[Parameter value] &&

Calendar[MonthID] <= MIN(Calendar[MonthID]))

with the above filter, you can then extract the dates

CALCULATE(MIN(Calendar[Date]),

FILTER(ALL(Calendar),Calendar[MonthID] >= MIN(Calendar[MonthID])-[Parameter value] &&

Calendar[MonthID] <= MIN(Calendar[MonthID]))
)

Re: Last date in period issue when use DatesBetween

QQB — Mon, 15 Jul 2024 13:19:02 GMT

Thank you! your suggested solution is so much better!