DAX - Help with FILTER - ALL option

Question

I have a DAX query below (QUERY1)&nbsp;where I am trying to display data fomr current week and data from prior week. Prior week is defined as "Actual GI Time Intelligence Fiscal Week Key -10" and the corresponding data as "Delivery Net Weight Quantity -10" and "Item Net Weight Quantity -10". The results for the prior week comes out as null. &nbsp;However, when I create a new query (QUERY2) with same parameter for the specifc prior week, I get data.&nbsp;Attaching screen shots from DaxStudio of both queries with results.&nbsp;//QUERY1//-----------evaluate&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;summarize&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;calculatetable&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'DELIVERY ITEM'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key] = 667&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Delivery Net Weight Quantity",[Delivery Net Weight Quantity]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Item Net Weight Quantity",[Item Net Weight Quantity]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Actual GI Time Intelligence Fiscal Week Key -10",'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]-10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Delivery Net Weight Quantity -10",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;calculate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Delivery Net Weight Quantity],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;filter(ALL('_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key] = '_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]-10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Item Net Weight Quantity -10",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;calculate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Item Net Weight Quantity],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;filter(ALL('_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key] = '_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]-10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;//QUERY2//-----------evaluate&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;summarize&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;calculatetable&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'DELIVERY ITEM'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key] = 657&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,'_ACTUAL GI DATE'[Actual GI Time Intelligence Fiscal Week Key]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Delivery Net Weight Quantity",[Delivery Net Weight Quantity]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,"Item Net Weight Quantity",[Item Net Weight Quantity]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-diye-msft · Answer

Hi&nbsp;jtc900502&nbsp;,
&nbsp;
Could you please share your dummy pbix here? Or the simple worksheet with serveral expected results.
That would be easier for us to provide the valuable advice.
&nbsp;
&nbsp;

jtc900502 · Answer

I don't really have a dummy PBIX, just working in DAX to setup the dataset before creating an SSRS report. It was just a general question on how to properly create a measure for a prior week data. I tried using filter ALL and CalculateTable but to no avail.&nbsp;

v-diye-msft · Answer

Hi&nbsp;jtc900502&nbsp;,
&nbsp;
I can barely imagine your data with your formula thus i can hardly provide the accurate suggestion. if you want to calculate the prior week data, please kindly refer to the similar post:&nbsp;
https://community.powerbi.com/t5/Desktop/To-fetch-previous-week-data/td-p/276276
https://community.powerbi.com/t5/Desktop/Measure-displaying-last-week-sum/td-p/495437https://community.powerbi.com/t5/DAX-Commands-and-Tips/Sales-amount-previous-week-snapshot/td-p/136315

Forum Discussion

DAX - Help with FILTER - ALL option

3 Replies

Related content

Saving filter options

Mass Filter (should not include Exclude option)

Datatable options

Monitor option

Source option missing