Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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...
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?
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |