Global Sales Analysis - Electronics Retailer| PowerBi

Key Insights:

• Highest Sales and Revenue are recorded in the holiday season of Dec- Feb, contributing 40% of the Total Sales.
• Offline Sales drive 80% of the revenue.
• Products falling in the Music/Movies/Audio category have the highest Profit Margin of 60%
• Over 5 years, the Average Delivery Time is reduced by 50% (8 days to 4 days).
• The US contributes to 55% of Online Orders.
• The rate of acquiring New Customers has reduced by 65% between 2019 - 2021.
• The rate of Returning Customers increased by 50% between 2018-2021.
• April records the highest churn rate over 400%, each year.
• 

Technical Details:

1. Currency Exchange: All the KPIs revolving revenue are dynamically calculated as per the latest exchange rates for the selected currency.

VAR SelectedCurrency = SELECTEDVALUE(
                            'Currency Seelction'[Currency],
                            "USD"
                            )
VAR SelectedDate = SELECTEDVALUE(
                        'Rolling Calendar'[Date],
                        CALCULATE(
                            MAX(
                                'Exchange Rates'[Date]
                                ),
                                FILTER(ALL('Exchange Rates'),
                                        'Exchange Rates'[Currency] = SelectedCurrency
                                )
                            )
)


VAR LatestRate = CALCULATE(
    MAX('Exchange Rates'[Exchange]),
  
    FILTER(ALL('Exchange Rates'),
     'Exchange Rates'[Currency] = SelectedCurrency &&
     'Exchange Rates'[Date] = SelectedDate
     )
)


var RevenueCurrency =SUMX(
    Sales,
    (Sales[Quantity]*
    RELATED(
        Products[Unit Price USD]
    )) *LatestRate
) 
    
RETURN
    RevenueCurrency

2. Customer Churn/Return/New Rate:

My goal was to display the monthly trend of customer loyalty. The business did a good job of building customer loyalty and trust and we can see by 2021 almost 80% are returning customers. Below is my Dax code to calculate returning customers, the expressions for new and churned are almost on similar lines.

Returning Customer = 
VAR MaxDate = SELECTEDVALUE(
    'Rolling Calendar'[Start of Month],
    MAX('Rolling Calendar'[Start of Month]))
VAR PrevMonthDate = EOMONTH(MaxDate, -2)+1

var customerList = All(Sales[CustomerKey]) 
RETURN 
COUNTROWS(
    FILTER(
        customerList,
            (CALCULATE(
                COUNTROWS('Sales'),
                FILTER(
                    All('Rolling Calendar'[Start of Month]),
                'Rolling Calendar'[Start of Month] < MaxDate 
            )) > 0)&&
            (CALCULATE(
                COUNTROWS('Sales'),
                FILTER(
                    All('Rolling Calendar'[Start of Month]),
                'Rolling Calendar'[Start of Month] = MaxDate 
            )) > 0)

        
    )
)

This is the first PowerBi dashboard I have added to my portfolio and I aim to contribute more to this space.