Last Week Turnover Filter Help Needed please

[Pablos_007]

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 !

[rautaniket0077 · Accepted 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.


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.

[Pablos_007]

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