Sales last year from specific set of users

[Anonymous]

Hi,

I have a table with an order date, a user ID, the amount of sales from the order and whether the user signed up for the newsletter. The order date is linked to my master date table.

ORDER DATE	USER ID	SALES	USER SIGNED UP FOR NEWSLETTER
JAN 1 2018	A	5	NO
FEB 1 2018	A	5	NO
MAR 1 2018	A	5	NO
APR 1 2018	A	5	NO
MAY 1 2019	A	5	NO
JAN 1 2019	A	10	NO
FEB 1 2019	A	20	NO
MAR 1 2019	A	30	YES
APR 1 2019	A	40	YES
MAY 1 2019	A	40	YES

I now want to get a better understanding of the year over year spending behavior of the users who signed up for the newsletter vs users who have not signed up for the newsletter. I tried the following formula:

CALCULATE(

    SUM('table'[SALES]),
    'table'[NEWSLETTER] = TRUE,

    DATEADD('Master Date'[Date],-1,YEAR))

This formula however is returning 0, as it cannot find a value from last year where the user was signed up for the newsletter. How do I change the formula to do that?

ORDER DATE	USER ID	SALES	USER SIGNED UP FOR NEWSLETTER	RESULT FROM MY FORMULA	RESULT I NEED
JAN 1 2018	A	5	NO	0
FEB 1 2018	A	6	NO	0
MAR 1 2018	A	7	NO	0
APR 1 2018	A	8	NO	0
MAY 1 2019	A	9	NO	0
JAN 1 2019	A	10	NO	0
FEB 1 2019	A	20	NO	0
MAR 1 2019	A	30	YES	0	7
APR 1 2019	A	40	YES	0	8
MAY 1 2019	A	40	YES	0	9

Regards

Bas

[ChandeepChhabra]

@Anonymous 

Please refer to the Power BI attached. Also why are the Sales values different in the two tables shown as example?

[Anonymous]

Hello @ChandeepChhabra ,

Thank you for taking the time to help me out. I've used your file but it's not giving me the results I want. I've amended it a bit to better explain what I'm looking for and tried to upload it, but I don't see an option to upload, so I've attached a screenshot instead.

I want to know for a specific month or a specific set of months in a given year what the amount of sales last year was for a user that is subscribed to the newsletter in that particular month.

I used your file to filter on year 2019, month April and only show me the users who signed up for the newsletter. The sales from last year column is now not showing any values. That makes sense, as last year the value for 'signed up for newsletter' was set to NO. And as there's a filter on 'signed up for newsletter' is set on YES, it's not showing any data of the previous year.
How can that formula be amended so that it's showing me the total sales from that user from last year?

Best regards

Bas

[ChandeepChhabra]

@Anonymous 

Just for my understanding, rephrasing it. You need to find

1. Total Sales for Current Period for only Subscribers (Yes)
2. Total Last Year Sales for current period Subscribers (where or not they were subscribers last year)

If the above is what you mean. Please revise the measures

Total CY Sales for Subsribers = 
CALCULATE(
    SUM(Data[SALES]),
    FILTER(
        Data,
        Data[USER SIGNED UP FOR NEWSLETTER] = "Yes"
    )
)

Sales LY = 
CALCULATE(
    SUM(Data[SALES]),
    SAMEPERIODLASTYEAR('Date Table'[Date])
)

Power BI File

In this case you won't be needing the Subscriber filter. The result would look like this

[Anonymous]

Hi @ChandeepChhabra ,

I'm sorry but that won't do either. That's similar to the calculation that I used in my initial question and it's returning a blank value for the last month Let me give you an other example with this data table:

Year	Customer	Sales	Signed up for newsletter
2018	A	10	No
2019	A	100	No
2018	B	80	No
2019	B	40	Yes
2018	C	20	No
2019	C	60	Yes

I now have three customers, and instead of months I've changed things to years.

Imagine that the year column is in fact showing dates, and that this is linked to a master date table.

Using this table I'm making a report. That report has the option to filter on years. I'm filtering that report on the year 2019, and it's showing me various other metrics like the total sales that was generated in 2019 (100+40+60=200) and the amount of customers that placed orders (3), and the amount of sales from customers that are subscribed to the newsletter (40+60=100).

I want to have an additional metric that shows me how much the people who have subscribed to the newsletter spent last year.

Customer A is not subscribed to the newsletter in 2019

Customer B subscribed to the newsletter and spent 40 this year and 80 last year. The formula should return 80.

Customer C subscribed to the newsletter and spent 60 this year and 20 last year. The formula should return 20.

In total this metric should show me a number of 100.

When I use your formula or my initial formula, the result is 0. Reason for that is because the formula is filtering on users who are subscribed, and as the user wasnt subscribed last year it's not picking up the data from last year. How can i get to the 100?

Many thanks for your efforts so far!

Bas

[ChandeepChhabra · Accepted solution]

Thanks for explaining. I think I have what you need, nevertheless please take a look

Two Measures

Total Sales = 
CALCULATE(
    SUM(Data[Sales]),
    FILTER(Data,Data[Signed up for newsletter]="Yes")
)

Customer Spent LY = 
VAR SubsCustomers = 
CALCULATETABLE(
    VALUES(Data[Customer]),
    Data[Signed up for newsletter]="Yes"
)
RETURN
CALCULATE(
    SUM(Data[Sales]),
    SAMEPERIODLASTYEAR('Date Table'[Date]),
    SubsCustomers
)

and Same Power BI link

[Anonymous]

Yes that's the one @ChandeepChhabra ! Thanks so much for taking the time to explain this to me. Much appreciated!

Best regards

Bas

[ChandeepChhabra]

@Anonymous Glad it worked as expected. Cheers