Total Sales for same month last year

[MikeEcho]

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

[v-ljerr-msft]

Hi @MikeEcho,

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

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

Regards

[MikeEcho]

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 · Accepted solution]

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. 

Regards

[MikeEcho]

Awesome v-ljerr-msft that worked and thanks for the explanation, makes sense why it did not work before. I will watch for this type of thing going forward 😉

[CheenuSing]

Hi @MikeEcho

1. I deleeted the data2 table from the pbix supplied by you.

2. Reloaded the data from datanew2 excel file provided by you.

3. established relationship once again.

4. everything worked as it should

Sample output

If this works for you acccept it as a solution and also give KUDOS.

Cheers

CheenuSing