Forum Discussion
DATESBETWEEN or DATESMTD not working
I am struggling to figure out how to get a set of dates for the latest, filtered month of dates in my model.
I have two measures which accurately show the range of dates I am looking to run my calculation by, but when I use either of the two functions below, I still get a month's worth of dates in the latest month of my Dates Table:
// Option 1 - Dates Between
_mtd = DATESBETWEEN(DateTable[Date], [StartOfMonth], [EndOfMonth])
// Option 2 - Dates MTD
_mtd = DATESMTD(DateTable[Date])
// Option 3 - Dates MTD with a filtered start
_mtd = CALCULATETABLE(DATESMTD(DateTable[Date]), DateTable[Date] = [StartOfMonth])
I'm not sure what I'm doing wrong, but hoping that this is something that's acheivable. I'm ultimately running calculations for current month, month/month, and year/year.
joe_darling , if you use datesmtd in a table, it will give static dates. and that will last available month in your date table
If you want dynamic content using these in a measure
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sWhy DATESMTD is still blank, even after having correct TI Setup: https://youtu.be/j0ug-XQgZtg
Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg
3 Replies
joe_darling , if you use datesmtd in a table, it will give static dates. and that will last available month in your date table
If you want dynamic content using these in a measure
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sWhy DATESMTD is still blank, even after having correct TI Setup: https://youtu.be/j0ug-XQgZtg
Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg
Amit,
thanks for the resources. The issue I'm having is not that I get blank values, but that all my filtering should dictate getting dates between 2/1/2023 and 2/28/2023, but instead I'm getting the latest month in my dates table (6/1/2023 to 6/30/2023).
my measure for StartOfMonth is defined as FIRSTDATE(DATESMTD(DateTable[Date])). (PERFECT > 2/1/2023 as expected)
my measure for EndOfMonth is LASTDATE(DATESMTD(DateTable[Date])). (PERFECT > 2/28/2023 as expected)
But when I just use DATESMTD(DateTable[Date]), I get all dates for June 2023, my latest month in the DateTable.
My desired output here is to have the all the dates of the latest month that I've filtered for. In my example here, I want all dates for February 2023 (last month in my filter range), not June 2023 (my last month in the table)
