replace the incremental sum with a value

[Krishna4work]

Hello there!

I am working on a report that gives on hand by date, but currenty we do not have any table that stores this data.

So i have to formulate this OH by date considering all the transaction.

There are 4 type of transactions that affects the OH i.e Sales, Transfers (slips), purchases(vouchers) and adjustments (used to adjust to a specific value).

I did the transformation to get the output. only major issue i am facing is because of the adjustment.

The scenario is

1) If its sale then the sale qty is subtracted from OH qty.

2) If its transfer slip then the transfer qty is subtracted from OH qty.

3) If its purchase voucher then the receiving qty is added to OH qty.

4) if its adjustment then the OH qty becomes the adjusted value.

I'm having issue with the forth bit.

i'm attaching the .pbix file in this WE-SHARE LINK

Appreciate your help regarding this report.

Thankyou!

[Anonymous · Accepted solution]

Hi @Krishna4work ,

Here I suggest you to try code as below to create a calculated column.

Qty af trans =
VAR _ADDCOLUMNS =
    ADDCOLUMNS (
        Append1,
        "ADJSUT",
            IF (
                Append1[Type] = "ADJUST",
                [Sum Qty],
                VAR _MAXDATE =
                    CALCULATE (
                        MAX ( Append1[DOC_CREATED_DATETIME] ),
                        FILTER (
                            Append1,
                            Append1[Type] = "ADJUST"
                                && Append1[ITEM_SID] = EARLIER ( Append1[ITEM_SID] )
                                && Append1[STORE_SID] = EARLIER ( Append1[STORE_SID] )
                                && Append1[DOC_CREATED_DATETIME] <= EARLIER ( Append1[DOC_CREATED_DATETIME] )
                        )
                    )
                VAR _QTY =
                    CALCULATE (
                        SUM ( Append1[Sum Qty] ),
                        FILTER (
                            Append1,
                            Append1[ITEM_SID] = EARLIER ( Append1[ITEM_SID] )
                                && Append1[STORE_SID] = EARLIER ( Append1[STORE_SID] )
                                && Append1[DOC_CREATED_DATETIME] = _MAXDATE
                        )
                    )
                RETURN
                    _QTY
            ),
        "NotAdjust",
            IF (
                Append1[Type] = "ADJUST",
                0,
                CALCULATE (
                    SUM ( Append1[Sum Qty] ),
                    FILTER (
                        Append1,
                        Append1[Type] <> "ADJUST"
                            && Append1[ITEM_SID] = EARLIER ( Append1[ITEM_SID] )
                            && Append1[STORE_SID] = EARLIER ( Append1[STORE_SID] )
                            && Append1[DOC_CREATED_DATETIME] <= EARLIER ( Append1[DOC_CREATED_DATETIME] )
                    )
                )
            )
    )
RETURN
    SUMX (
        FILTER (
            _ADDCOLUMNS,
            Append1[ITEM_SID] = EARLIER ( Append1[ITEM_SID] )
                && Append1[STORE_SID] = EARLIER ( Append1[STORE_SID] )
                && [DOC_CREATED_DATETIME] = EARLIER ( Append1[DOC_CREATED_DATETIME] )
        ),
        [NotAdjust] + [ADJSUT]
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.