topic Calculating last year sales dynamically based on slicer in DAX Commands and Tips

Calculating last year sales dynamically based on slicer

Joshua_rich15 — Tue, 12 Nov 2024 03:52:00 GMT

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

Re: Calculating last year sales dynamically based on slicer

Poojara_D12 — Tue, 12 Nov 2024 06:57:50 GMT

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

Re: Calculating last year sales dynamically based on slicer

Anonymous — Wed, 13 Nov 2024 02:26:35 GMT

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.

Re: Calculating last year sales dynamically based on slicer

Joshua_rich15 — Thu, 14 Nov 2024 01:00:10 GMT

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