Forum Discussion

remi113's avatar
remi113
New Member
3 years ago

Previous Month showing same value as Current Month

Hello everyone, I am trying to make a graphic (cards) comparing current month sales to previous month sales.  I am using a month-year sales table and a calendar table.  For some reason, I can not get it right.  Both values are always the same, no matter what month I select.  I am unsure if it is my DAX function or modeling schema, however I have tried every possibility of cardinality and cross filtering between the tables, and nothing fixes it.  I am using EDATE, but have also used DATEADD I think it was and a couple others.

 

I will upload an image that explains, the DAX function I am using is: 

 

PREVIOUSMONTH = CALCULATE(SUM('month-year sales'[monthly sales]),EDATE('Calender Table'[Date],-1))
 
Whenever I try to remove SUM, it gives me error: "A single value for column 'monthly sales' in table 'month-year sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
 
Thank you.
 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Please show the complete Data Model. 

    With what do you have the bidirectional relationship with your Month-Year Sales Table? 

    And talking of Month-Year Sales Table, what is the reasoning behind creating this table? I think you have created an additional table which was never required at first place, and hence made your Data Model complex. 

     

    As for the DAX, I would suggest using PREVIOUSMONTH to get the values for your last month, 

     

    PREVIOUSMONTH = CALCULATE(SUM('month-year sales'[monthly sales]), PREVIOUSMONTH('Calendar Table'[Date]))
    • remi113's avatar
      remi113
      New Member
      3 years ago

      Hi, I will attach an image of my data model.  I honestly am not sure why I created a separate table, I just knew I needed this data so created it in Excel and imported it, I am new to PowerBI and modeling, this is my first project.  When I use PREVIOUSMONTH, the DAX accepts it, but returns (Blank) for every month in PREVIOUSMONTH.  Here is my data model.