Total Sales for same month last year

I have looked everywhere, and the examples I did find don't seem to work.

I understand how to do the YTD.MTD's est, but cannot find one of the probably most simple formulas and that is:

How do I calculate the total Monthly sales for last year same month?,
I need something like this
[Client] [dMonth] [dMonth Sales] [dSales LY]
998983 201701 $1,150,214.00 $1,540,214.00
234494 201702 $3,130,112.00 $2,730,332.00
564983 201703 $2,345,336.00 $1,567,589.00
344566 201704 $1,885,336.00 $1,999,543.00
345568 201705 $2,456,886.00 $1,897,433.00

Any idea what the formula for [Sales LY] should be?
I did setup the Calendar table and linked it to my debtors table (drsSalesledger) on date.
I only seem to get blanks for [Sales LY] or the same value as current month.
My Full Calendar table is called Calendar.

The [dMonth] I use:
dMonth = FORMAT(drsSalesledger[dDate], "YYYYMM")

The [dMonth Sales] I use:
dMonth Sales = sum(drsSalesledger[dSalesVal])

The [dSales LY] I use:
dSales LY = calculate([drsSalesledger], parallelperiod('Calendar'[Date],-12,months))

Any assistance would be appreciated

Need Help

v-ljerr-msft
9 years ago
Hi MikeEcho,

After reviewing your shared pbix file, I find that most of your dbdate values are DateTime type which has a time value that is not "12:00:00 AM". That's why most of them are not matched with the Dates in Calendar table(with a time value of "12:00:00 AM").

So I use the formula below to add a new calculate column called "dbdate2" to get only the Date part of dbdate column in data2 table, and recreate the relationship with dbdate2 column between these two tables, then all works as expected.

dbdate2 = DATEVALUE(data2[dbdate])

Here is the modified pbix file for your reference. :smileyhappy:

Regards

5 Replies

v-ljerr-msft
Microsoft Employee
9 years ago
Hi MikeEcho,

Could you try using the formula below to create a new measure to see if it work in your scenario? :smileyhappy:

dSales LY = CALCULATE ( [drsSalesledger], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

Regards
- MikeEcho
  Frequent Visitor
  9 years ago
  I tried that and it did not work. So I decided to setup a sample dataset using excel to test and see what is going wrong.
  
  The problem seems to be related to the Calendar table as it is obviously not doing the relationship as I expected. I am obviously doing something wrong, or missing something.
  
  Any help appreciated.
  
  Not sure how to attach my PowerBi and dataset it is located here
  http://www.24web.co.za/powerbi/datanew2.xlsx
  http://www.24web.co.za/powerbi/daterelationshiptest.pbix
  - v-ljerr-msft
    Microsoft Employee
    9 years ago
    Hi MikeEcho,
    
    After reviewing your shared pbix file, I find that most of your dbdate values are DateTime type which has a time value that is not "12:00:00 AM". That's why most of them are not matched with the Dates in Calendar table(with a time value of "12:00:00 AM").
    
    So I use the formula below to add a new calculate column called "dbdate2" to get only the Date part of dbdate column in data2 table, and recreate the relationship with dbdate2 column between these two tables, then all works as expected.
    
    dbdate2 = DATEVALUE(data2[dbdate])
    
    Here is the modified pbix file for your reference. :smileyhappy:
    
    Regards