Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

sum last month not working when using DAX function ADDDATE create measure(bi-relationship)

Hi I have a problem with creating the measure 'sum value last month'. 

really need your help!

 

cross filter direction

  if i SET data model with single relation,   ---the ADDDATE not working

    if I set data model with bi-relation, ---system not output results

 

 

I create a 'calendar' table called 'calendar' having filed called (Dates) use DAX based on my un-continues date in the main table called 'fdate', which only has 1 day per month.

 

I am use
      sum actual LM = CALCULATE([sum value], DATEADD(Calendar[Date],-1, MONTH) the system
 
has the error:
Error Message: MdxScript(Model) (16, 101) Calculation error in measure 'kicksall'[sum mactual LM]: Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.
 
then I changed bi-direction relation back to default single relation. I can have my sum actual LM,
but the value of 'sum actual LM' exactly the same as 'sum actual' sum in the same month. n (seems the data intelligent function not working) any suggestion?
 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Have marked Calendar as date column. This will check all dates are continuous

    Hope you created Calendar using Calendar function not distinct of date from tables

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi, sorry, I am not sure how to 'marked Calendar as date column' any suggestion?

      I use this DAX created my 'Calendar' table.

       

      Calendar =

      VAR Days = CALENDAR (DATE ( YEAR(MIN(kicksall[fdate])) ;1 ;1 ); DATE(YEAR(MAX(kicksall[fdate]));12 ;31 ) )

      RETURN ADDCOLUMNS (

          Days;

          "Year"; YEAR ( [Date] );

          "Month Number"; MONTH ( [Date] );

          "Month"; FORMAT ( [Date]; "mmmm" );

          "Year Month Number"; YEAR ( [Date] ) * 12 + MONTH ( [Date] )- 1;

      "Year Month"; FORMAT ( [Date]; "mmm yy" );

          "Quarter"; FORMAT ( [Date]; "\Qq");

         "Year Quarter"; FORMAT ( [Date]; "yyyy \Qq" )

      )

      so if I do not have the bi-relationship, calender date itself is continues