DAX Measure reference error

[ViorelCa]

Dear Colleagues,

I get an error when I am referencing the following Measure.

CurrentMonth:=CALCULATE(
MONTH(Max(Merge1[Date])),
Merge1[SalesR2017]<>0)

The value of this CurrentMonth measure is 4 (as April is the last month with sales).

If I am using the following formula, with MONTH = 4, it's OK:

Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(Merge1[Date])=4)

But if I am using the Measure CurrentMonth instead of 4, I get an error:

Sales2017Rcurrent:=CALCULATE(sum(Merge1[SalesR2017]),MONTH(Merge1[Date])=[CurrentMonth])

Why it's not working when referencing the Measure?

Kind regards,

Viorel

[ViorelCa]

Another strange thing happens if I am using the Date column from the Calendar Table instead of the Date Column from the Fact Table (in the Fact Table I have a Sales column for Plan 2017 with all 12 months filled and a Sales column for Real 2017 with sales only from January to April).

If I calculat the curent month using the Fact table Date column the result is correct = 4

CurrentMonth:=CALCULATE(
MONTH(MAX(Merge1[Date])),
Merge1[SalesR2017]<>0)

But if I am using the Date column from the Calendar table, it results 12 (not correct):

CurrentMonthv2:=CALCULATE(

MONTH(MAX(CalendarTable[Calendar Date])),

Merge1[SalesR2017]<>0)

Many thanks,
Viorel

[Anonymous]

ok, here is my last try...

Sales2017Rcurrent = CALCULATE(
	sum(TableA[Sales]);
	filter(TableA;
		MONTH(TableA[Date])=MONTH(max(TableA[Date]))))

result with my data for 2017:

Hope this helps.........

[ViorelCa]

Hi @Anonymous

Thanks a lot for your effort, I will have to learn more DAX, as I guess my Data Model is wrong.

Your measure returns 0 (as it would have been month 12, which does not have sales).

Sales2017Rcurrentv6:=CALCULATE(

SUM(Merge1[SalesR2017]),

FILTER(Merge1,Merge1[SalesR2017]<>0,

MONTH(Merge1[Date])=MONTH(MAX(Merge1[Date]))))

Returns 0.

If I try to add another filter parametar in order to "force" the last month with sales, it returns #ERROR.

Sales2017Rcurrentv6:=CALCULATE(

SUM(Merge1[SalesR2017]),

FILTER(Merge1,

Merge1[SalesR2017]<>0,

MONTH(Merge1[Date])=MONTH(MAX(Merge1[Date]))))

Returns #ERROR.

I will go back to study more thoroughly Rob Collie's Power Pivot's book.

Thanks anyway and all the best!

Viorel

[Anonymous]

Hi @ViorelCa

Try this one:

Measure = CALCULATE(
                                       sum(Merge1[SalesR2017]),
                                       MONTH(Merge1[Date])=MONTH(Max(Merge1[Date]))
                    )

[ViorelCa]

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

[Dog]

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

[ViorelCa]

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

[Anonymous]

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?.

[ViorelCa]

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

[Anonymous]

@Dog@ViorelCa

Sorry for my measure, didn't double check it.

So, the measure which works for me is:

MEASURE = CALCULATE(sum(Merge1[SalesR2017]); LASTNONBLANK(Merge1[SALESR2017];">0"))

btw, do use a time table (aka calendar)?

[ViorelCa]

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