Forum Discussion

EnrichedUser's avatar
EnrichedUser
Helper III
4 years ago

Filter Multiple Tables Based on Date Table

My goal is to create a messure [Sales] that will be calculated based on the same date field.

 

My Tables:

Table 1: Date

  • All Unique Dates
  • Active Relationship to SKU Adds

Table 2: SKU Adds

  • Unique ID (Site_Item_key)
  • Date Added col

Table 3: Receipts

  • ID (Site_Item_key)
  • Receipt Date

Table 4: Invoice History

  • ID (Site_Item_key)
  • Invoice Date

Expected Output:

Sum of Invoice amount for IDs with 'invoice date' in same period as 'Date Added'

 

Notes:

 

Sales =

CALCULATE( SUMX('Invoice History', 'Invoice History'[Unit Price] * 'Invoice History'[QTY Invoiced]),

USERELATIONSHIP('Date'[Date], 'Invoice History'[Invoice Date]) )

 

  • Will provide sales for correct year but ignores date added
  • >- Part added in 2020, should calculate to 0 sales for 2021

 

 

Sales =

CALCULATE( SUMX('Invoice History', 'Invoice History'[Unit Price] * 'Invoice History'[QTY Invoiced]),

USERELATIONSHIP('Date'[Date], 'Invoice History'[Invoice Date]),
USERELATIONSHIP('Date'[Date], 'SKU Adds'[Date Added]) )

 

  • Will show blank for sales in 2021 for material added in 2020;
  • Shows some of total sales for all years instead of just 2020 when filter of 2020 is applied

 

I've also tried cross filter.

 

I would like to make this more dynamic. Specifically, calculate sales within 365 days of data add

- Invoice Date <= Date Added(365)

 

 

Need Help

3 Replies

  • McDermoW5's avatar
    McDermoW5
    New Member
    4 years ago

    Connecting using both the 'Invoice History'[Invoice Date] and the 'SKU Adds'[Date Added] is only giving you items where the [Date Added] and [Invoice Date] are the same. This means that since the item wasn't added in 2021, there are no instances where both [Date Added] and [Invoice Date] were the same and so it won't show anything in 2020. 

    For the [Date Added] you should use FILTER('SKU Adds', 'SKU Adds'[Date Added] >= EARLIER('Date'[Date])) rather than using a connection.

     

    Whole expression would be: 

    Sales =

    CALCULATE( SUMX('Invoice History', 'Invoice History'[Unit Price] * 'Invoice History'[QTY Invoiced]),

    USERELATIONSHIP('Date'[Date], 'Invoice History'[Invoice Date]),
    FILTER('SKU Adds', 'SKU Adds'[Date Added] >= EARLIER('Date'[Date])) )

     

    More generally, connections are a powerful tool but circular connections like those cause these sort of issues and it's definitely something you need to be wary of. In the same way as PBI made some of your conections inactive by default, you should avoid using all of the connections if it creates that circular shape and replace on with a filter if necessary.

    • EnrichedUser's avatar
      EnrichedUser
      Helper III
      4 years ago

      Thank you for your reply. I was super excited to try this. 

      I understand your advice fairly well and will continue to try a few other way. As is, it does not work and gives the following error:

      "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

       

      - The Earlier function doesnt prepopulate with any options.


      I have never gotten earlier to work in a dax messure. I normally use it in calculated columns instead. Im thinking I need to make a summary table use ADDCOLUMNS and filter that virtual table. Any other thoughts?




       

    • EnrichedUser's avatar
      EnrichedUser
      Helper III
      4 years ago
      Sales_1 = 
VAR StartDate = MIN('SKU Adds'[Date Added])
VAR EndDate = ENDOFYEAR('SKU Adds'[Date Added])
VAR Results = CALCULATE([Sales], FILTER('Invoice History', 'Invoice History'[Invoice Date] <= EndDate && 'Invoice History'[Invoice Date] >= StartDate)
)
Return Results

       

      Looks like this is working as intended. Any thoughts?