Forum Discussion

PaoloCorrado's avatar
PaoloCorrado
Frequent Visitor
3 years ago
Solved

Problem - Comparing Years Sales of same Customer Current year and PY (only customers)

Hi All,

I have this problem:

I should only compare customers who have sales in current year and previous year ( ineed the measure, no calculate column) are in DQ.

I tried to use this solution, Solved: Comparing Current and Past Year Sales - Microsoft Power BI Community

I can't show the PY of customers who bought this year (Same Sales PY).

 

 

This is the result I expect: If i filter year 2022 only customers A and B have sales in selected year and previous year.

Name_Customers

Basic_Sales Amount

Basic_PY Sales Amount

Same Sales Current Year

Same Sales PY

A

980

520

980

520

B

710

950

710

950

C

 

460

  

D

440

   
 

2130

1930

1690

1470

 

Download example pbix: (WeTransfer)

Comparing Years Sales 

Data:

Date

ID Cliente

SalesAmount

01/01/2021

A

100

01/02/2021

A

150

01/03/2021

B

250

01/04/2021

A

50

01/05/2021

A

120

01/06/2021

B

300

01/07/2021

B

250

01/08/2021

C

100

01/09/2021

C

160

01/10/2021

C

200

01/11/2021

B

150

01/12/2021

A

100

01/01/2022

A

600

01/02/2022

A

280

01/03/2022

B

200

01/04/2022

D

30

01/05/2022

D

80

01/06/2022

B

110

01/07/2022

B

250

01/08/2022

D

80

01/09/2022

D

140

01/10/2022

D

110

01/11/2022

B

150

01/12/2022

A

100

 

Any help or guidance would be amazing.

 

Grazie!

Paolo

 

 

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    PaoloCorrado,

     

    Try this measure:

     

    Same Sales PY = 
// clients in the filtered year
VAR vCurrentYearClients =
    SUMMARIZE ( Sales, Customers[ID_Customer] )
// calculate last year's sales for only the clients who have sales in the filtered year
VAR vResult =
    CALCULATE (
        SUM ( Sales[SalesAmount] ),
        SAMEPERIODLASTYEAR ( Dim_Date[Date] ),
        vCurrentYearClients
    )
RETURN
    vResult

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    PaoloCorrado,

     

    Try this measure:

     

    Same Sales PY = 
// clients in the filtered year
VAR vCurrentYearClients =
    SUMMARIZE ( Sales, Customers[ID_Customer] )
// calculate last year's sales for only the clients who have sales in the filtered year
VAR vResult =
    CALCULATE (
        SUM ( Sales[SalesAmount] ),
        SAMEPERIODLASTYEAR ( Dim_Date[Date] ),
        vCurrentYearClients
    )
RETURN
    vResult