Forum Discussion

PBISunK's avatar
PBISunK
Frequent Visitor
1 year ago

DAX formula for Inventory Projections multiplying Item's Quantity, Cost by date

I have 3 DIM tables Item, Item Cost & Date and a fact containing Inventory Quantity.  Item, Item Cost & Inventory Quantity are all joined by Item_SK using 1:M relationships. Date Dim & Inventory quantity are joined by Date_SK field. I would like to form a DAX formula to multiply Item Quantity from Inventory Quantities Fact with Item Cost using the intermediate joiner table Item. While joining to Item_Cost table, it needs to consider COST_YEAR criteria (to compare if its current year and use current year's cost & also use future year for projections). 

 

Tables  & Columns  

Item Dim: Item_SK, Item_ID, Item_Name,....

Item Cost Dim: Item_SK, Unit_Cost, Cost_Year,....

Date Dim: Date_SK, Date, Month, Year, ....

Inventory Fact: Item_SK, Date_SK, Item_Quantity

 

Visual 1

Reporting would to show 

By Month:

Month, Item_ID, Item_Name, Item_Quantity,  Unit_Cost, Item_Cost (Quantity*Unit_Cost)

 

Visual 2

By Month:

Overall Item_Cost projections into the future by looking at the ITem_Cost based on YEAR into the future (2025, 2026) 

 

Here is the snippet of DAX that I am working with but for single Item, the visuals work, but for all or multiple items the report doesnt do the Cost multiplication by each Item. Somehow I need the item wise quantity * Cost calculated before I can sum them all for Visual 2 

 

DAX:

IF(
    MIN('Date'[Date]) >= [Snapshot Since],
    IF(
        YEAR(MIN('Date'[Date])) = YEAR([Snapshot Since]) + 1,
        CALCULATE(
            CALCULATE(
                [Inv Qty],
                DATESBETWEEN('Date'[Date], [Snapshot Since], [Snapshot Since])
            ) *
            MAXX(
                FILTER(
                    'Item Cost',
                    'Item Cost'[COST_YEAR] = YEAR(MIN('Date'[Date]))
                ),
                'Item Cost'[COST]
            ),
            USERELATIONSHIP('Item'[Item SK], 'Item Cost'[Item SK])
        ),
        IF(
            YEAR(MIN('Date'[Date])) = YEAR([Snapshot Since]) + 2,
            CALCULATE(
                CALCULATE(
                    [Inv Qty],
                    DATESBETWEEN('Date'[Date], [Snapshot Since], [Snapshot Since])
                ) *
                MAXX(
                    FILTER(
                        'Item Cost',
                        'Item Cost'[COST_YEAR] = YEAR(MIN('Date'[Date])) - 1
                    ),
                    'Item Cost'[COST]
                ),
                USERELATIONSHIP('Item'[Item SK], 'Item Cost'[Item SK])
            ),
            CALCULATE(
                [FHM Inv Cost],
                DATESBETWEEN('Date'[Date], [Snapshot Since], [Snapshot Since])
            )
        )
    ),
    BLANK()
)
Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi PBISunK - You can create a measure to retrieve the unit cost based on the current year and future years:

    // Unit Cost based on year criteria
    Item Unit Cost =
    VAR CurrentYear = YEAR(TODAY())
    VAR SelectedYear = SELECTEDVALUE('Date'[Year])
    RETURN
    CALCULATE(
    MAX('Item Cost'[Unit_Cost]),
    FILTER(
    'Item Cost',
    'Item Cost'[Item_SK] = MAX('Item'[Item_SK]) &&
    (
    (SelectedYear = CurrentYear && 'Item Cost'[Cost_Year] = CurrentYear) ||
    (SelectedYear > CurrentYear && 'Item Cost'[Cost_Year] = SelectedYear)
    )
    )
    )

    // Total Item Cost calculation by multiplying quantity by cost
    Item Total Cost =
    SUMX(
    'Inventory Fact',
    'Inventory Fact'[Item_Quantity] * [Item Unit Cost]
    )

     

    Try the above modified one and i hope it works.