Calculate value between two dates

[Anonymous]

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
product  date               number of products

A             01.02.2019    1

A              01.05.2019    2

I would like to calculate the value of cost from table A in table B 
So it should look like this:

product  date               number of products  cost          Cost of goods

A             01.02.2019    1                                  100           1*100 = 100

A              01.05.2019    2                                  150           2*150 = 300

[Mariusz · Accepted solution]

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.

[Mariusz]

Hi @Anonymous 

You can do it in Query Editor like below 
Price table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSBob6QGRkYGgJ5BgDOcYQTqwOVJUpTJUJTJVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product Code" = _t, Cost = _t, startDate = _t, endDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Code", type text}, {"Cost", Int64.Type}, {"startDate", type date}, {"endDate", type date}})
in
    #"Changed Type"

Quantity table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0jcyMLQEcgyVYnUQwqYwYSMUYTMk1bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, date = _t, #"number of Products" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"date", type date}, {"number of Products", Int64.Type}}),
    #"Added Custom" =Table.AddColumn(#"Changed Type", "Cost", each let d = [date] in Table.SelectRows(ProductPrice, each [startDate] <= d and (if [endDate] <> null then [endDate] >= d else true))[Cost]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Cost"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Cost", type number}})
in
    #"Changed Type1"

Regards,
Mariusz

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

[Anonymous]

@Mariusz  thx for your reply

Looks pretty complicate, is there a way to do it with dax/calculated columns?

[hthota · Accepted solution]

Please try this:

Column =

CALCULATE(

MAX('Query1'[Cost]),

FILTER(

RELATEDTABLE('Query1'),

'Query1'[startDate]<= Query2[date] )

)*Query2[number of Products]

[Mariusz · Accepted solution]

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]

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]

@hthota 

This will not work, missing out the whole timedimension.

On every row will be 450 (sum cost)