Reply
jaineshpoojara
Frequent Visitor
Partially syndicated - Outbound

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!!!
jaineshpoojara_0-1716880263395.png

 

@Ritaf1983 @lbendlin @amitchandak @Ashish_Mathur @ryan_mayu 

3 REPLIES 3
lbendlin
Super User
Super User

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





lukiz84
Memorable Member
Memorable Member

Syndicated - Outbound

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

 

Differences between DATEADD and PARALLELPERIOD in DAX - SQLBI

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)