Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

 

Screenshot 2020-04-22 at 15.56.10.png

 

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?
 
Screenshot 2020-04-22 at 15.54.25.png
2 REPLIES 2
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.