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

     

     

     

Anushka's avatar
Anushka
New Member
8 years ago

Hi,

 

You need to have a relationship beween the two tables to achieve this. (Better if you have numeric column rather than creating relationship between supplier columns.)

 

Then you can create a calculated column in Spend table , something like below.

 

ValueSpent= IF([PO Date] > RELATED(Contract[EffectiveDate].[Date]) && [Date] < RELATED(Contract[EndDate]), Spend[Value] , 0) 

 

Finally, get the sum of the newly created column. 

 

Hope this helps

 

 

Thanks