Forum Discussion

ViorelCa's avatar
ViorelCa
Helper I
9 years ago

DAX Measure reference error

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 mea...
Need Help
ViorelCa's avatar
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's avatar
    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's avatar
      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