Calculate Year over Year comparison dynamically

[Anonymous]

I have sales data spanning the last 8 years.

In my dashboard, I've included two slicers—one for selecting the year and another for selecting the month.

When a user selects a year and a month, such as 2024 and April (04), the current year's data spans from April 2024 to April 2023, while the previous year's data spans from March 2023 to March 2022. I want to perform a Year-over-Year (YOY) comparison of sales between these two time frames."

"Similarly, if the user selects 2023 and January (01) in the slicers, I aim to compare sales from January 2023 to January 2022 against sales from December 2021 to January 2021."

Please help me in resolving this issue.

[Ashish_Mathur]

Hi,

On selecting April 2024, shouldn't the period be May 2023 - April 2024 i.e. 12 months?  Share the download link of the PBI file.

[v-jialongy-msft · Accepted solution]

Hi @Anonymous 

Here's a general approach to achieve this:

1. Create a Date Table: If you don't already have one, create a Date table that includes all dates within your sales data range and make sure it's marked as a Date table in Power BI. This table should have columns for Year and Month.

2. Establish Relationships: Ensure that your Date table is related to your Sales data table using the Date column.

3. Create Measures for Sales: Create measures in your Sales data table to calculate the total sales. 

Total Sales = SUM(Sales[Amount])

4. Create a Measure for Current Period Sales: Create a measure that calculates the sales for the selected period. 

Current Period Sales = 
   CALCULATE(
       [Total Sales],
       FILTER(
           ALL('Date'),
           'Date'[Year] = SELECTEDVALUE('Date'[Year]) &&
           'Date'[MonthNumber] = SELECTEDVALUE('Date'[MonthNumber])
       )
   )

5. Create a Measure for Previous Period Sales: Create a measure that calculates the sales for the same period in the previous year. 

Previous Period Sales = 
   CALCULATE(
       [Total Sales],
       FILTER(
           ALL('Date'),
           'Date'[Year] = SELECTEDVALUE('Date'[Year]) - 1 &&
           'Date'[MonthNumber] = SELECTEDVALUE('Date'[MonthNumber])
       )
   )

6. Create a Measure for YOY Comparison: Finally, create a measure to compare the Current Period Sales with the Previous Period Sales. 

YOY Sales Difference = [Current Period Sales] - [Previous Period Sales]

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@rajendraongole1 
kindly help.