Forum Discussion
DAX Measure reference error
Hi Anonymous
The same error.
Actually I am doing this in PowerPivot Excel 2013 and the error does not not have a message, it just shows #ERROR
Hi,
I wonder if it's having an issue build the query to run.
does this work any better.
Sales2017Rcurrent:=
var lastmonth = MONTH(max(Dates[DateKey]))
return
CALCULATE(sum(Merge1[[SalesR2017]), MONTH(Merge1[Date]) = lastmonth, Merge1[SalesR2017] <> 0)
actually you don't need to last part as summing a zero doesn't matter
Sales2017Rcurrent:=
var lastmonth = MONTH(max(Dates[DateKey]))
return
CALCULATE(sum(Merge1[[SalesR2017]), MONTH(Merge1[Date]) = lastmonth)
Dog
Hi Anonymous,
Your measure is working, but is not giving sum of the sales of the last month (as I want), but the last nonblank item in the column.
I am using a Calendar table, and even if I am refering to the Date Column in the Calendar table, I get the same #ERROR
Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(CalendarTable[Calendar Date])=[CurrentMonth])
Maibe my Data Model is not good:
In the Fact Table I have a column with the budget sales (all months) and another column with the Real sales (Only Jan-Apr) that will be updated each month with the corresponding sales.
What I try to do is to dinamically sum the sales of the current month, by declaring first that measure
CurrentMonth:=CALCULATE(
MONTH(MAX(Merge1[Date])),
Merge1[SalesR2017]<>0)
that returns 4,
and then summing the sales of current month by this second measure (that;s not working).
Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(CalendarTable[Calendar Date])=[CurrentMonth])
This second measure works only if I am refering the current month (4) non-dinamically, like this:
Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(CalendarTable[Calendar Date])=4)
Maibe I should change something in the Data Model?
Many thanks,
Viorel
Hi Dog
On both measures I get the expression invalid/incomplete error (syntax for lastmonth is incorrect).
Maibe my Data Model is not good:
In the Fact Table I have a column with the budget sales (all months) and another column with the Real sales (only Jan-Apr) that will be updated each month with the corresponding sales.
What I try to do is to dinamically sum the sales of the current month, by declaring first that measure
CurrentMonth:=CALCULATE(
MONTH(MAX(Merge1[Date])),
Merge1[SalesR2017]<>0)
that returns 4 (as it is April),
and then summing the sales of current month by this second measure (that;s not working).
Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(CalendarTable[Calendar Date])=[CurrentMonth])
This second measure works only if I am refering the current month (4) non-dinamically, like this:
Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(CalendarTable[Calendar Date])=4)
Maibe I should change something in the Data Model?
Many thanks,
Viorel
Hi ViorelCa
I checked my measure once again. It works for my data.
I would need more details about your data model to help you. Without I can't help you. Sorry.
Maybe @MattAllington can help here?.
Hi Anonymous
This is the Table:
StoreCode StoreName Date SalesPlan2017 SalesReal2017 xxx yyy 1/1/2017 A B xxx yyy 2/1/2017 A B xxx yyy 3/1/2017 A B xxx yyy 4/1/2017 A B xxx yyy 5/1/2017 A 0 xxx yyy 6/1/2017 A 0 xxx yyy 7/1/2017 A 0 xxx yyy 8/1/2017 A 0 xxx yyy 9/1/2017 A 0 xxx yyy 10/1/2017 A 0 xxx yyy 11/1/2017 A 0 xxx yyy 12/1/2017 A 0 I need to calculate dinamically the sales only for the current month (April).
Many thanks,
Viorel