Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ckcalvins
New Member

An invalid numeric representation of a date value was encountered

I'm drawing a blank here as to why I'm getting an error.

 

I'm trying to create a DAX measure that calculates the gross bookings WoW based on dynamic date ranges.  The most recent 7 days versus the previous 7 days before it.  It works just fine and the measure calculates within Power Pivot with data I've validated.

 

Sum of BOOKED_ROOMS_WOW:=(CALCULATE(Gross_Bookings[Sum of BOOKED_ROOMS],DATESBETWEEN('Calendar'[Date],LASTDATE(Gross_Bookings[RESERVATION_DATE])-6,LASTDATE(Gross_Bookings[RESERVATION_DATE]))))-(CALCULATE(Gross_Bookings[Sum of BOOKED_ROOMS],DATESBETWEEN('Calendar'[Date],LASTDATE(Gross_Bookings[RESERVATION_DATE])-13,LASTDATE(Gross_Bookings[RESERVATION_DATE])-7)))

 

When I bring it into a pivot table and try to use it slice this measure with my various dimensions inside the pivot table, some of the dimensions give me an error: mdxscript (model) (6,105) error Calculation error in measure 'Gross_Bookings'[Sum of BOOKED_ROOMS_WOW]: an invalid numeric representation of a date value was encountered.  Why?  This is killing me...Pivot Table Error.png

 

Power Pivot & DAX Formula.png

3 REPLIES 3
lbendlin
Super User
Super User

Maybe rewrite with variables and see if there's any change.

 

Sum of BOOKED_ROOMS_WOW:=
var ld = LASTDATE(Gross_Bookings[RESERVATION_DATE])
return 
CALCULATE(
Gross_Bookings[Sum of BOOKED_ROOMS]
,DATESBETWEEN('Calendar'[Date],ld-6,ld)
)
-
CALCULATE(
Gross_Bookings[Sum of BOOKED_ROOMS]
,DATESBETWEEN('Calendar'[Date],ld-13,ld-7)
)

Didn't work unfortunately.  Formula errors.

What's the error message?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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