Dynamics Community Forum Thread Details

Inventory Value Storage Report - How to replicate logic in PowerBI?

Hello there. I need to replicate the inventory value report in a PowerBI dashboard. I was able to replicate the inventory value for each item and total value only on the current date. If I run the report on today's date, it matches perfectly with my PowerBI.
However, if I filter back in time, there is a mismatch between what I calculate and what the report shows. I assume there is a logic I am missing, can anyone please advice?

This is my PowerBI measure to calculate the InventoryValue based on the InventTrans data.

InventoryValue = 
VAR SelectedDate = [SelectedDate]
RETURN
SUMX(
    'InventTrans',
    IF(
        ('InventTrans'[StatusIssue] IN {"None", "Sold"} && 
        'InventTrans'[StatusReceipt] IN {"None", "Purchased"}) &&
        ('InventTrans'[DateFinancial]  <= SelectedDate &&
        'InventTrans'[DateFinancial] <> DATE(1900,1,1) + TIME(12,0,0)),
        'InventTrans'[CostAmountPosted] + 'InventTrans'[CostAmountAdjustment],
        IF(
            ('InventTrans'[StatusIssue] IN {"None", "Sold" , "Deducted"} &&
            'InventTrans'[StatusReceipt] IN {"None", "Purchased", "Received"}) &&
            'InventTrans'[DatePhysical] <= SelectedDate,
            'InventTrans'[CostAmountPhysical],
            0
        )
    )
)

The logic is the following:
"For each row, sum the CostAmountPosted + CostAmountAdjustment if there is a DateFinancial and its before the currently selected date and the transaction's status is Sold or Purchased.
Else, if the DatePhysical is before the SelectedDate and matches as Sold/Deducted and Purchased/Received, then use just CostAmountPhysical"

I have created a filtering DateFilter column with the following logic:

CAST(
    CASE
        WHEN it.DateFinancial <> '1900-01-01 12:00:00 +00:00' AND it.DateFinancial < it.DatePhysical 
            THEN it.DateFinancial
        ELSE it.DatePhysical
    END AS datetimeoffset
) AS DateFilter

"If there is a DateFinancial and it's before the DatePhysical, then this will be where my transaction's date becomes valid, else use DatePhysical"

With this, DateFilter is used to filter down the transactions rows which were available on a certain SelectedDate.

I think there is an addittional logic on when to sum the AdjustmentCost probably. Thanks in advance for your help! :D

Quick Links

Leaderboard