Calculation that takes values prior to the filter date, when the current value is empty

[Syndicate_Admin]

Hello! Need help please! I need to calculate the profitability of a business per month, I have the column sales and quantities sold, and the column cost U. All purchases and sales have month and year and are related to a calendar table.

Not for all items sold in a month I have "cost U" in that same month, some items were purchased in past months. I would need you to use the last cost U for that product in that case. (respecting the current filter, i.e. not the last cost of the entire table, looking for the cost in the months prior to the filtered month)

I made a "last purchase" cost, but I can't get it to work with the visual filter of the page.

When I want to see January sales, I would need to have costs U of January or the last one before that month...

Last Buy Cost =

WHERE _lastDATE= CALCULATE( LASTDATE(Shopping[Date]), filter(Shopping, Shopping[Code]= EARLIER(Shopping[Code])))

return

CALCULATE(Shopping[6-Cost], filter(shopping, Shopping[Code]= EARLIER(Shopping[Code]) && Shopping[Date]= _lastDATE))

Thanks a lot!!

[v-shex-msft]

HI @Natalia0710- ,

Power bi current does not support create dynamic calculate column/table based on filter selections.They work on different levels, and you can't apply the interaction from the child level to affect their parent levels.

For this scenario, I think you need to create measure formula instead.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng