Forum Discussion

Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
3 years ago
Solved

Filtering 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 ...
  • tamerj1's avatar
    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] )
    )