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

Hi ViorelCa Try this one:Measure = CALCULATE( sum(Merge1[SalesR2017]), MONTH(Merge1[Date])=MONTH(Max(Merge1[Date])) )

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]))returnCALCULATE(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]))returnCALCULATE(sum(Merge1[[SalesR2017]), MONTH(Merge1[Date]) = lastmonth) Dog

DogViorelCaSorry 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)?

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

DAX Measure reference error | Microsoft Fabric Community

11 Replies

Anonymous
Not applicable
9 years ago
Hi ViorelCa

Try this one:
Measure = CALCULATE( sum(Merge1[SalesR2017]), MONTH(Merge1[Date])=MONTH(Max(Merge1[Date])) )
- ViorelCa
  Helper I
  9 years ago
  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
    Responsive Resident
    9 years ago
    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
Helper I
9 years ago
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
  Not applicable
  9 years ago
  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
    Helper I
    9 years ago
    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

Forum Discussion

DAX Measure reference error

11 Replies

Related content

Reference a measure in another measure

Circular reference error

dynamic reference line

Circular Reference error with no circular reference

Reference measure