Hi, DAX gurus! 🙂
We have a challange on calculating profit or loss statement for a specific vending devices in a specific location, taking into account that devices are being moved from time to time between serveral locations.
We have two tables – one which contains our expense transactions for a specific devices (date | device id| cost sum) and other table contains device movement history between locations (movement date | device id | new location id).
What we need is to create a column in expense table that would represent the corresponging location value of the device from most recent movement entry (date of latest movement entry equals or is before expense transaction date). In other words – i need to identify where the device was located on the expense transaction date.
Any ideas/suggestions how to solve this?
P.S. We also do have a calendar table in our datamodel (linked by date column to both above mentioned tables) and device table that contains unic values of device id's linked by device ID to both previously mentioned tables.
