Forum Discussion

nesselman's avatar
nesselman
Icon for Helper I rankHelper I
3 years ago
Solved

Calculating a SUM if Date is Less Table Date

What I'm trying to do is SUM all the Inventory if the CategoryEnding Date is <= the Weekending Date.

 

TABLE1 is a simple calendar

TABLE2 is a table with Ending Date and Inventory

NO Relationships (because that ties the dates together)

 

= CALCULATE(SUM([Inventory]),[CategoryEnding]<='TABLE1'[Weekending])

This gives "expecting a true/false

 

How can I write this DAX measure please?

 

TABLE1 TABLE2     
Weekending CategoryEndingInventory   
10/14/2022 10/28/2022$1,000    
10/21/2022 11/11/2022$5,000    
10/28/2022 11/18/2022$500    
11/4/2022 12/2/2022$7,500    
11/11/2022       
11/18/2022       
11/25/2022       
12/2/2022       
        
        
PBI Table       
WeekEndingTotal to CategoryEnding Notes    
10/14/2022$14,000 Since all Dates are > Weekending Date they SUM
10/21/2022$14,000      
10/28/2022$14,000      
11/4/2022$13,000 lose the 10/28 Category Ending 
11/11/2022$13,000      
11/18/2022$8,000 lose the 11/11 Category Ending 
11/25/2022$7,500 lose the 11/18 Category Ending 
12/2/2022$7,500      
12/9/2022$0 lose the 12/2 Category Ending  
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    nesselman , if they are not related you can work on selected value or min or max

     

    example

     

    CALCULATE(SUM(Table2[Inventory]),filter(Table2, table2[CategoryEnding]<=max('TABLE1'[Weekending]) ))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    nesselman , if they are not related you can work on selected value or min or max

     

    example

     

    CALCULATE(SUM(Table2[Inventory]),filter(Table2, table2[CategoryEnding]<=max('TABLE1'[Weekending]) ))

    • nesselman's avatar
      nesselman
      Icon for Helper I rankHelper I
      3 years ago

      Yes, that worked using "max".  I needed it the other way so I used:

      >=MAX

       

       

       

  • Thennarasu_R's avatar
    Thennarasu_R
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Measure= var A=CALCULATE(SUM([Inventory]),userelationship(CategoryEnding Date ,Weekending Date)
                   Var Result=calculate(A,Filter(Table,Table(MinCategoryEnding Date)<=max(table,Weekending Date)
    return
        result