Daily valuation of BOM's (backup table?)

[pawelj795]

Hi,
I have a quite complicated issue in Power BI.

I have 2 tables.
The first one contains my BOM structure.

The second one contains prices of materials to mentioned BOM's. 
These prices are changing daily.

Now, I want to have a valuation of my BOM's for each day.
How to achieve this?

Sample file:
https://drive.google.com/file/d/1NVhaRj6YX5xu0cD-W-rscigno_PwBKri/view?usp=sharing

My idea was to create something like a backup table for each day with my BOM's valuation.
Similar topic:
https://community.powerbi.com/t5/Desktop/How-can-i-take-a-monthly-backup-of-my-table-and-insert-it-i...

Another idea is to somehow filter table "BOM" with dates from "Prices", but how can I create a relationship with LOOKUPVALUE function?

[v-yiruan-msft · Accepted solution]

Hi @pawelj795 ,

You can update the formula of the calculated column as below:

Cost = 
CALCULATE (
    MAX ( 'Prices'[Unit Price] ),
    FILTER (
        'Prices',
        'Prices'[Component Name] = 'BOM'[Component Name]
            && 'Prices'[Update Date] = TODAY ()
    )
)

If the above one is not what you want, please provide your expected result with specific example and backend logic. Thank you.

Best Regards

[pawelj795]

.

[pawelj795]

Maybe should I use dynamic M query parameters?