Forum Discussion

PBIX1234's avatar
PBIX1234
Frequent Visitor
3 years ago

DAX Help - Calculate sum if between two dates in another table

Hi, I have two fact tables; Spend and Contracts. 

 

I am trying to make a forumla for 'Spend under Contract', but not getting the desired result.

 

SPEND

DateSupplierCategoryAmount
1/1/20XYZA$100

 

CONTRACTS

SupplierCategoryStartEndStatus
XYZA1/1/1931/12/19Active

 

I need to SUM the Amount from SPEND, only if it occurs betweem the Start & End Dates from CONTRACTS, with the exception that if it occurs after the End Date, as long as the status is "Active" it is sill included.

 

My thoughts were something like this, but does not work;

 

Contracted Spend = CALCULATE( SUM(SPEND[Amount]) ,

FILTER( CONTRACTS, SPEND[Date] >= CONTRACTS[Start Date]  && SPEND[Date] >= CONTRACTS[Start Date] || CONTRACTS[Status] = "Active" )

 

I will be filtering by Supplier & Category, which have their own joining tables, hence cannot use "ALL" functions.

I have tried variations with SUMX too, but my DAX is failing me...

 

Sample file here if it helps.

 

Thank you in advance.

Need Help

1 Reply

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

    PBIX1234 , try like

     

    Contracted Spend = Sumx( Contract, CALCULATE( SUM(SPEND[Amount]) ,

    FILTER( SPEND, SPEND[Date] >= Min(CONTRACTS[Start Date]) && SPEND[Date] >= max(CONTRACTS[Start Date])
    && SPEND[Supplier] >= max(CONTRACTS[Supplier])
    && SPEND[category] >= max(CONTRACTS[category])
    || CONTRACTS[Status] = "Active" ))