cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Sales last year from specific set of users

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]),

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

1 ACCEPTED SOLUTION
Impactful Individual

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

7 REPLIES 7
Impactful Individual

@Anonymous

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

Anonymous
Not applicable

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

Impactful Individual

@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
Not applicable

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

Impactful Individual

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
Not applicable

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

Best regards

Bas

Impactful Individual

@Anonymous Glad it worked as expected. Cheers

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors