Filtering Sales with Dates

[Tob_P]

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 Entry'[Posting Date]),

TREATAS(VALUES('NAV_Stand Items'[Item No_]), 'NAV_Sales History MASTER'[Item No])

)+0

Can anyone suggest an amendment to it so that it will only include Sales when the Posting Date in 'NAV_Sales History MASTER, is greater than or equal to the Posting Date in 'Nav_Iten Ledger Entry?

[tamerj1 · Accepted solution]

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

[Tob_P]

Hi @tamerj1 

Thank you for taking the time to look at this one, sorry for delay in coming back to you too. Works a treat so thank you, but yes, you are absolutely right about perfomance. It's very resource heavy!

[Tob_P]

@tamerj1 

Here is link to a sample version of the report for anyone who would like to offer any guidance

[tamerj1]

Hi @Tob_P 

how does your report look like? What columns are involved in you visual?