Forum Discussion

mahra-in's avatar
mahra-in
Helper II
8 years ago
Solved

Calculate value between two dates if date falls between two dates in another table

Hi   I need your help in calculating the sum of values if the date of that table falls in between two dates from another table   Table 1: Spend   Supplier Category Value PO Date ABB M...
Need Help
  • ChandeepChhabra's avatar
    ChandeepChhabra
    8 years ago

    Hi, You can download the solution excel (powerpivot)

     

    A few things that I have done

     

    1. I have created a relationship between Client Name between Spend and Contract Table, assuming that the client name will not be duplicated in the Contract Table
    2. Then I have written the following measure

     

    Spend Between Dates = CALCULATE(SUM(Spend[Value]),FILTER(Spend,Spend[PO Date]>=RELATED(Contract[Effective Date])&&Spend[PO Date]<=RELATED(Contract[End Date])))

     

    Snapshot of the result

     

     

     

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
8 years ago

Hi, You can download the solution excel (powerpivot)

 

A few things that I have done

 

  1. I have created a relationship between Client Name between Spend and Contract Table, assuming that the client name will not be duplicated in the Contract Table
  2. Then I have written the following measure

 

Spend Between Dates = CALCULATE(SUM(Spend[Value]),FILTER(Spend,Spend[PO Date]>=RELATED(Contract[Effective Date])&&Spend[PO Date]<=RELATED(Contract[End Date])))

 

Snapshot of the result

 

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi! Could you tell me please what should I do if between theese two tables is another one? This table keep all information about client account. Relationships is created by "account id".