Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate value between two dates

Hi,    I have the following two tables:   Table A product cost  startdate         enddate A            100   01.01.2019     31.03.2019 A             150  01.04.2019     ......   Table B pro...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

     

    Please try the below.

    Column = 
CALCULATE( 
    MAX('Table A'[Cost]), 
    FILTER(
        RELATEDTABLE('Table A'),
        'Table A'[startDate] <= 'Table B'[date] )
)

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • hthota's avatar
    hthota
    7 years ago

    Please try this:

     

    Column =
    CALCULATE(
    MAX('Query1'[Cost]),
    FILTER(
    RELATEDTABLE('Query1'),
    'Query1'[startDate]<= Query2[date] )
    )*Query2[number of Products]
     
hthota's avatar
hthota
Resolver III
7 years ago

Hi,

 

Create a Measure in First table as given below.

Measue = sum(Table1[Cost])

Later create the Calculated column in Second table as given below.

Cost of Goods= Table1[Number of Probucts]*Measure

 

Please make sure the correct relationships between the two tables.

 

Thanks,

Hemanth Thota.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hthota 

     

    This will not work, missing out the whole timedimension.

    On every row will be 450 (sum cost)