Forum Discussion
Last date in period issue when use DatesBetween
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!
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]))
)
2 Replies
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]))
)Thank you! your suggested solution is so much better!
