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

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Tob_P 

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

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

    tamerj1 

     

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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] )
)
    • Tob_P's avatar
      Tob_P
      Icon for Helper V rankHelper V
      3 years ago

      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!