Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

1 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

 

 

Capture.png

 

View solution in original post

7 REPLIES 7
ChandeepChhabra
Impactful Individual
Impactful Individual

@Anonymous 

 

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

 

Capture.png

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.

 

Capture.PNG

 

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

 

 

@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

Capture.png

 

Anonymous
Not applicable

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

 

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

 

 

Capture.png

 

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

@Anonymous Glad it worked as expected. Cheers

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors