Forum Discussion
Tob_P
Helper V
3 years agoFiltering Sales with Dates
I have the following Measure which works well based on my model.. Total Sales (Stands) = CALCULATE([Total Sales], USERELATIONSHIP('NAV_Sales History MASTER'[Posting Date], 'NAV_Item Ledger ...
- 3 years ago
Hi Tob_P
It was a bit confusing as TP2 Posting date is 12/06/2020 which is before the very first date in the sample sales table. See below screenshot. However, I believe the proposed solution is workable but I have serious doubts about the performance. Please refer to attached sample file amended with the solution.Total Sales On and After = SUMX ( GENERATE ( 'Nav_Item Ledger Entry', CALCULATETABLE ( VALUES ( 'Nav_Stand Items'[Item No_] ) ) ), VAR PostingDate = 'Nav_Item Ledger Entry'[Posting Date] VAR CurrentItem = 'Nav_Stand Items'[Item No_] VAR CurrentCustomerSales = CALCULATETABLE ( 'Nav_Sales History Master' ) VAR FilteredSalesTable = FILTER ( CurrentCustomerSales, 'Nav_Sales History Master'[Item No] = CurrentItem && 'Nav_Sales History Master'[Posting Date] >= PostingDate ) RETURN SUMX ( FilteredSalesTable, 'Nav_Sales History Master'[Sales] ) )