Calculate Year over Year comparison dynamically

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.