Forum Discussion
Solved
Last Week Turnover Filter Help Needed please
Trying to use DAX in Microsoft Excel - Help sought please 😉 I created a measure named [Total Turnover 2023] (=CALCULATE(SUM(ASC_TBI_TBL[Turnover]),ALL(ASC_TBI_TBL[FY]),ASC_TBI_TBL[FY]="2023") ...
Try using below measure
LW_Turnover_2023 =var a = SELECTEDVALUE('Qry_Accounting Periods'[_Week])var b =CALCULATE((SUM(ASC_TBI_TBL[Turnover]),'Qry_Accounting Periods'[_Week] = a-1)return b
use one slicer for week and another for year (2023)
as you can see in the below screenshot i have selected year = 2015 and week = 8.
dont use this below measure
(=CALCULATE(SUM(ASC_TBI_TBL[Turnover]),ALL(ASC_TBI_TBL[FY]),ASC_TBI_TBL[FY]="2023")if it solves your issue please accept my answer as solution.
Try using below measure
LW_Turnover_2023 =
var a = SELECTEDVALUE('Qry_Accounting Periods'[_Week])
var b =
CALCULATE(
(SUM(ASC_TBI_TBL[Turnover]),
'Qry_Accounting Periods'[_Week] = a-1
)
return b
use one slicer for week and another for year (2023)
as you can see in the below screenshot i have selected year = 2015 and week = 8.
(=CALCULATE(SUM(ASC_TBI_TBL[Turnover]),ALL(ASC_TBI_TBL[FY]),ASC_TBI_TBL[FY]="2023")
use one slicer for week and another for year (2023)
as you can see in the below screenshot i have selected year = 2015 and week = 8.
dont use this below measure
(=CALCULATE(SUM(ASC_TBI_TBL[Turnover]),ALL(ASC_TBI_TBL[FY]),ASC_TBI_TBL[FY]="2023")
if it solves your issue please accept my answer as solution.
Huge Kudos rautaniket0077 🙂 owe you a coffee 😉
In Microsoft Excel, I had to tweak the DAX as SELECTEDVALUE only works in Power BI desktop. Solution:-
=CALCULATE(SUM(ASC_TBI_TBL[Turnover]),
'Qry_Accounting Periods'[_Week] = IF(HASONEVALUE(ASC_TBI_TBL[Week Number]),VALUES(ASC_TBI_TBL[Week Number])-1))