Forum Discussion

mnarmeen's avatar
mnarmeen
Advocate I
9 years ago
Solved

Previous Month

Hello BI users,   I have a problem which has many related posts but still couldnt get my chart to work. So any help would be appreciated   I have to show a value of total sales which is alrea...
Need Help
Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi mnarmeen,

    After research and test, you should create a Calendar table, and create a relationship with your 'erie_sale' table by date column.

    Then create a measure using the formula, then add it to your visual.

    PreviousSales = CALCULATE(SUM(erie_sale[Total]),PREVIOUSMONTH(Calendar[date]))


    Best Regards,
    Angelia

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi mnarmeen,

    I know your calculated column is [filterdate]. I think it still works, Please try and respond me if you have any issue.

    Best Regards,
    Angelia

mnarmeen's avatar
mnarmeen
Advocate I
9 years ago

v-huizhn-msft

 

Thankyou so much for your answer. I have created a table dimdate and made a relation with my sales table. But the problem is im trying to show the sum of quantity stored in a different table (sale_enteries) which has relation with my sales table on the basis of invoice id.

 

Furthermore, date in sales table is a calculated column (based on some criteria).

Do you think your solution will work if I create relationship with a calculated column ?

dimdate(date) with sales(date which is a calculated column)

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi mnarmeen,

I know your calculated column is [filterdate]. I think it still works, Please try and respond me if you have any issue.

Best Regards,
Angelia

  • mnarmeen's avatar
    mnarmeen
    Advocate I
    9 years ago

    Thankyou for your reply.

    So, what I have done so far is that I have created a dimdate in sql and populated it with dates and now im making a relation (1-M) with my date column which isnt working .

    RelationDiagram

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi mnarmeen,

      Do you mind share your .pbix file for further analysis?

      Best Regards,
      Angelia