- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

DAX Formula
Greetings Power BI Community,
I'm encountering an issue with the DAX that I have used in my report, I have used DATESMTD and SAMEPERIODLASTYEAR but the problem is, it should return only the value till date i.e. "28-05-2023" but it returns values till 31-05-2023
I have checked my date table as well, it contains date till "TODAY()" only. Still it returns date till 31-05-2023 instead of current date (28-05-2023).
I've attached the image for the reference, please do the needful!!
Thank you for the time and consideration!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That is standard behavior for these functions. You need to bring your own additional "Is today or earlier in the previous year" filter. If your calendar table refreshes daily you can add a calculated column for that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @jaineshpoojara - The issue you're facing with DAX functions such as DATESMTD and SAMEPERIODLASTYEAR returning values beyond the current date likely stems from the way these functions are interacting with your date table.
Dates =
ADDCOLUMNS (
CALENDAR (DATE(2023,1,1), TODAY()),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & QUARTER([Date]),
"YearMonth", YEAR([Date]) * 100 + MONTH([Date])
)
-- Mark as Date Table
Model.AddCalculatedTable("Dates", Dates)
For current date:
SalesSPLY =
CALCULATE (
[SalesMTDAdjusted],
SAMEPERIODLASTYEAR(DATESBETWEEN('Dates'[Date], STARTOFMONTH(TODAY()), TODAY()))
)
Add a date slicer to the report to dynamically adjust the date range and verify that the measures behave correctly with the slicer set to <= TODAY()
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Proud to be a Super User! | |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I know you won't because you want a quick answer, but read this:
Differences between DATEADD and PARALLELPERIOD in DAX - SQLBI

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
10-08-2024 04:55 AM | |||
07-20-2024 02:04 AM | |||
11-22-2024 06:32 AM | |||
09-26-2024 05:30 AM | |||
11-10-2024 10:14 AM |
User | Count |
---|---|
90 | |
82 | |
57 | |
41 | |
39 |