How to create a comparison measure for my sales data?

[ianwuk]

Hello All.

I have Power Bi hooked up to an Excel spreadsheet for its data.  The data is daily sales data for each dat from 2017 up until today and looks like this.

Date     Sale Amount    Commission   Sales Name

1/5/18    100                      50              John James

3/8/17     250                     175            John James

12/12/17  300                     75             Clare Bridges

As you can see, my Excel spreadsheet shows sales for multiple sales persons and there is sales data from 2017 and 2018.

Inside Power Bi I extrapolated from the sales date the week number, month name, month number from the date as new columns that were added. 

So in PowerBi it looked like this.

Date     Sale Amount    Commission   Sales Name    Week Number    Month Name   Month number

1/5/18    100                      50              John James           1                             May                  5

3/8/17     250                     175            John James            1                            August             8

12/12/17  300                     75            John James             2                            December        12

3/1/18      400                      23            John James            1                            January            1

I then added a slicer to my Power Bi report so I filter data by month number, week number and year for each sales person.

My question is, how can I create a measure that will compare the sales amount in 2017 with the sales amount in 2018 for any given sales person.

So I want the final data to look like this:

Sales Name      Total Sales in 2017     Total Sales in 2018   Difference

John James         550                                     500                     -50

The measure would be Difference and it would take total sales from 2018 and minus it from total sales in 2017 to get the differnce value.

How can I do this?  I have lots of salespersons, surely I don't have to incorporate them all in to the measure do I?

Thanks.

[NipponSahore]

You can also pivot the table if you have an excel source 

Create a new Columns called Year = Year(datevalue)

Then pivot the data,You will have 2 columns for 2017 Sales and 2018 Sales

[Stachu]

you can hardocde the years filters to 2017/2018, but I think it's better to have them reference your time filter dynamically.
These 3 measures should work fine

CY Sales=SUM('Table'[Sale Amount])
PY Sales=CALCULATE([CY Sales],PARALLELPERIOD('Table'[Date],-1,YEAR))
Difference=[CY Sales]-[PY Sales]

[ianwuk]

Thanks @Stachu, I have more questions please.

If I have an existing measure that calculates total sales for each salesperson called Sales(!Refunds) in a spreadsheet called SalesData how would that affect your measure? 

My data looks like this:

SalesPerson    M#      W#           Year          Sales(!Refunds)  Total Sales Count

John James     5               2          2017                       1000                    3

John James     5               3          2018                        1500                   4

How would this plug in to your measures please?

Many thanks.

[ianwuk]

Hello @Stachu.

So far I now have this:

CY Sales = SUM('Sales2018'[Total Cost]) - Total Cost is the sales amount made.

PY Sales = PY Sales = CALCULATE([CY Sales],PARALLELPERIOD('AffiliatesMarch2018'[Created],-1,YEAR)) - Created is the order date.

This measure has an error and doesn't work - what did I do wrong?

The error is:

Calculation error in measure 'Sales2018'[PY Sales]: A data column containing duplicate dates was specified in the call to function 'PARALLELPERIOD'.  This is not supported.

As far as I know, there are no duplicate dates in the Created (order date) information in my main data.

Thanks.