How to calculate value based on lookup on multiple date columns

[rsanyoto]

Hi everyone,

Currently i'm stuck with the following requirements:

• Tabel Fact_ValueEntry contains transactions based on columns  [ProductionOrderFK] (updated), [ValueEntryPK], [PostingDate] and [ItemFK].
• Table Dim_PurchasePrice contains information about prices of items based on [StartingDate] en [EndingDate] and [ItemFK].
• A relationship of Many To Many is created between these two tables.

I need to calculate the correct value of column [DirectUnitCost] based on each [ProductionOrderFK] the following condition:

If a transaction in table Fact_ValueEntry has a PostingDate that comes in the range between [StartingDate] en [EndingDate], then show the value of [DirectUnitCost], otherwise null.

For example: the correct value of column [DirectUnitCost] for ItemFK : MXM30.0780 thats created on 30/4/2020 would be 1,57.

Many to Many on ItemPK : ItemPKFact_ValueEntryDim_PurchasePrice

What would be a good Measure in this case?

Kind Regards,

Regazzi

[rsanyoto]

Has anyone a better suggestions? Kind regards

[amitchandak]

@rsanyoto , you can create a flag column in first table

New col =

var _cnt = countx(filter(Table2, Table1[Posting Date]>= Table2[Start Date]  && Table1[Posting Date]<= Table2[End Date] )), Table2[Item FK])

return

if(Isblank(_cnt), false(), true())

[rsanyoto]

hI @amitchandak ,

the option you gave me doesnt meet my requirements. it's because the expected output must not be a true/false but a number.

Please see the following table as a expected result

ProductionOrderFK	ItemFK	PostingDate	StartingDate	EndingDate	DirectUnitCost
VPO-200776	MXM30.0780	30/04/2020	01/04/2020	31/08/2020	1,57

The 1,57 amount is created becasue there was a transaction on 30th april and thats a date between period of 01 april and 31 of augustus.

[v-kelly-msft · Accepted solution]

Hi @rsanyoto ,

Many to Many relationship is always troublesome.

You'd better create a dim table between  the 2 tables which is using 1:M relatioship to connect the 2 tables and get the DirectUnitCost values.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!