Forum Discussion
Units Previous Month - Two Date Tables
I am trying to calculate the units fulfilled in a previous month. My table has two date tables. I need the data from the "Fulfillment" date table.
This is the DAX formula I wrote. I received this error.
Units Previous Month = calculate(
sum('Orders Table'[net_quantity]),
filter(all('Date'),
'Date'[Month]= SELECTEDVALUE('Date'[Month])-1), 'Date'[Year]=SELECTEDVALUE('Date'[Year]))
ERROR: "
MdxScript(Model) (32, 52) Calculation error in measure 'Key Measures'[Units Previous M]: Cannot convert value 'January' of type Text to type Number."
MdxScript(Model) (32, 52) Calculation error in measure 'Key Measures'[Units Previous M]: Cannot convert value 'January' of type Text to type Number."
My table looks like this, and the data time is marked as "Date/time". I am able to mark the "Date Hierarchy" as the date table, but the formula still doesn't work, and then the month name doesn't work in the other date table.
Is there a different formula I can use?? I tried using a DATEADD formula, but it didn't return the correct values.
2 Replies
- danextian
Super User
Hi Anonymous ,
It is odd that it doesnt work. You can see in my screenshot below that it does.
Alternatively, try this
Previous Month2 = VAR __end = MIN ( Dates[Date] ) - 1 VAR __start = EOMONTH ( MIN ( Dates[Date] ), -2 ) + 1 RETURN CALCULATE ( [Sum of Values], DATESBETWEEN ( Dates[Date], __start, __end ) )
