Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pablos_007
Regular Visitor

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")

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 !

 

 

1 ACCEPTED SOLUTION
rautaniket0077
Resolver I
Resolver I

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.


rautaniket0077_0-1679906595212.png

 

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.

View solution in original post

2 REPLIES 2
rautaniket0077
Resolver I
Resolver I

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.


rautaniket0077_0-1679906595212.png

 

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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors