Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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)
With this measure in place:
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
Thank you so much guys! Appreciate the solutions, both worked fine. 🙂
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.
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)
With this measure in place:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
27 | |
23 | |
22 | |
22 |