Determine date at which sales price is updated based on contract change schedule

[mattknight1986]

Having dabbled somewhat in PowerBI at the beginning of this year, I've inevitably forgotten pretty much everything I learned and have gotten stuck upon trying to do some more modelling now.

I have Sales table with a [Transaction_Date] (from April 1 2017 to March 31 2018), and a separate SupplierContracts table with [Unit_Price] and [Effective_Date] (also from April 1 2017 to March 31 2018) for a [Contract_Title] and [Product_Code] (there may be multiple changes in the year, or the contract may not appear at all in which case I assume the effective date is 1 April).

In the sales data, there is a lag between the Unit Price becomming effective as per the SupplierContracts and being passed on to the Customer. I need to be able to establish the most recent Effective_Date so I can then calculate the time lag (in days) to the Sales_Price being updated. (i.e. find the largest SuplierContracts[Effective_Date].[Date] which is less than Sales[Transaction_Date].[Date], or else return 1 Apr 2018 for each [Contract_Title] and [Product_Code])

I'm having trouble applying examples shown elsewhere to my situation. 

nb - I have a DateDim table, distinct ProductCode table and distinct ContractTitle table anchoring the relationships between Sales and SupplierContracts (see attached). Can probably provide data if needed, just needs loads of scrubbing and cutting down.

 

Any help much appreciated.

Thanks

Matt

[mattknight1986]

FWIW, I'm looking to build a solution similar to this one, but instead of returning the RateOnDate, I'm after the date itself, but can't quite figure it out.

I've got this so far: 

EffectiveDate = 
    Calculate(  VALUES(ContractUpdate[Effective Date]),
                topn(   1,
                        Calculatetable( ContractUpdate,
                                        ContractUpdate[Contract Title] = earlier(CombinedData[Contract Title]),
                                        ContractUpdate[NPC] = earlier(CombinedData[NPC]),
                                        ContractUpdate[Effective Date] <=EARLIER(CombinedData[Transaction_date]
                                        )
                        ),
                        ContractUpdate[Effective Date],
                        desc
                )
    )

but to no avail (it returns three distinct values—two dates and a blank—out of about 228). NB, I've tried a number of approaches and either had this result or only blanks. SO I'm not sure where I'm going wrong.

Thanks in advance

MK

[mattknight1986]

I've still not been able to figure this out at all.  Any advice would be much appreciated.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490