Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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")
Fact table is ASC_TBI_TBL
In my fact table, I created a column named Customer_Product
Date Table is Qry_Accounting Periods
My date table goes from 01/01/2023 to 31/12/2023 with a column named _Week that has entries from 1 to 52.
In my date table, I created two columns in PowerQuery a StartOfWeek Date and EndOfWeek Date
I am trying to attain the previous week Turnover on the column Customer_Product. If slicer pick week 7 measure gives week 6 Turnover. If week 11, get week 10 Turnover etc..
My effort:-
LW_Turnover_2023=CALCULATE([Total Turnover 2023],FILTER(ALLSELECTED('Qry_Accounting Periods'),'Qry_Accounting Periods'[_Week]=MIN('Qry_Accounting Periods'[_Week])-1))
This works if I view _Week as row and add two measures, [Total Turnover 2023] and [LW_Turnover_2023]
However, if i add Customer_Product it doesn't.
Somehow I need to Filter on ASC_TBI_TBL Table on column ASC_TBI_TBL[Customer_Product]
So close but so far 😞
Help !
Solved! Go to Solution.
Try using below measure
dont use this below measure
Try using below measure
dont use this below measure
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))
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |