DAX Formula

[jaineshpoojara]

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!!!

@Ritaf1983 @lbendlin @amitchandak @Ashish_Mathur @ryan_mayu 

[lbendlin]

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.

[rajendraongole1]

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 !!

[lukiz84]

I know you won't because you want a quick answer, but read this:

Differences between DATEADD and PARALLELPERIOD in DAX - SQLBI