Calculating last year sales dynamically based on slicer

[Joshua_rich15]

Hey all,

I need to provide our sales team with a dashboard that can be filtered to view previous years sales. Currently I have a sales table by category that has both a current year (TY) and last year (LY) columns, which works perfectly fine. However, when I apply a slicer to this, specifically the financial year, the LY data displays null.

3p Sales FY23 =

var years = SELECTEDVALUE('BI vw_FinCalWeek'[Fin_Year])-1

var sales=

         CALCULATE(

            sum('BI vw_DailyWrittenSalesMetrics'[SubBeforeTax]),

            filter('BI vw_DailyWrittenSalesMetrics','BI vw_DailyWrittenSalesMetrics'[Dropship]="Y"),

            filter('BI vw_DailyWrittenSalesMetrics','BI vw_DailyWrittenSalesMetrics'[FinYear]=years)

          )

return

if(sales=blank(),value(0),sales)

The expected result of this is that when a user selects a financial year, IE 2024, that the LY column will show 2023. Likewise if they selected 2023, then 2022 sales would show.

Unfortunately we do not use a date/time column to calculate sales in a period, rather a numeric value for that date (202401), so I cannot use the DATEADD or the SAMEPERIODLASTYEAR.

Thanks!
JR

[Joshua_rich15]

Thank you so much guys! Appreciate the solutions, both worked fine. 🙂

[v-kaiyue-msft]

Hi @Joshua_rich15 ,

You can try the expression below.

3p Sales FY23 = 
VAR SelectedYear = SELECTEDVALUE('BI vw_FinCalWeek'[Fin_Year])
VAR PreviousYear1 = SelectedYear - 1
VAR Sales =
    CALCULATE(
        SUM('BI vw_DailyWrittenSalesMetrics'[SubBeforeTax]),
        FILTER(ALL('BI vw_DailyWrittenSalesMetrics'), 'BI vw_DailyWrittenSalesMetrics'[Dropship] = "Y" &&
        'BI vw_DailyWrittenSalesMetrics'[FinYear] = PreviousYear1
    ))
RETURN
    IF(ISBLANK(Sales), 0, Sales)

In addition, I created some data according to your description and attached it, you can see more details there.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Poojara_D12 · Accepted solution]

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