Calculating last year sales dynamically based on slicer

Hi Joshua_rich15 

Please try with the DAX given below:

3p Sales LY =
VAR SelectedYear = SELECTEDVALUE('BI vw_FinCalWeek'[Fin_Year]) - 1
VAR LY_Sales =
    CALCULATE(
        SUM('BI vw_DailyWrittenSalesMetrics'[SubBeforeTax]),
        'BI vw_DailyWrittenSalesMetrics'[Dropship] = "Y",
        'BI vw_DailyWrittenSalesMetrics'[FinYear] = SelectedYear
    )
RETURN
    IF(NOT ISBLANK(LY_Sales), LY_Sales, 0)

Using the Measure in Your Dashboard

With this measure in place:

• Add the measure to your visuals that display the LY values.
• Ensure your slicer for Fin_Year is connected to the BI vw_FinCalWeek table so that selecting a financial year filters the measure correctly.

Additional Notes

If you encounter cases where sales for the previous year don’t exist, this measure will still return 0 due to the IF check. Also, make sure there’s a relationship between the BI vw_FinCalWeek and BI vw_DailyWrittenSalesMetrics tables on a relevant field, which is essential for the slicer to affect the LY_Sales measure.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant