Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |