Combine actual sales with planned sales with inventory

[joshua1990]

I have a table / matrix with the following structure:

Year-Week	WeekIndex	Inventory	Planned Sales	Sales	Inventory Planned
2024-01	-1	5	1	2	5
2024-02	0	4	2	1	4
2024-03	1		2		3
2024-04	2		0		3

I would like to create the last column based on the following filters:

• If WeekIndex <=0 then take the actual Inventory from column 3
• If WeekIndex >0 then substract the Inventory by the planned Sales

How would you do that with a DAX measure?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Expected result measure: =
SUMX (
    VALUES ( Data[Year-Week] ),
    CALCULATE (
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Data[WeekIndex] ) <= 0, [Inventory measure:],
            SELECTEDVALUE ( Data[WeekIndex] ) > 0, [Inventory measure:] - [Planned Sales measure:]
        )
    )
)

[v-yangliu-msft · Accepted solution]

Thanks for the reply from Jihwan_Kim , please allow me to provide another insight: 
Hi  @joshua1990 ,

Here are the steps you can follow：

1. Create calculated column.

Column =
IF(
    'Table'[WeekIndex]>0,
    SUMX(
    FILTER(ALL('Table'),[WeekIndex]>0),[Inventory] - [Planned Sales]),
    SUMX(
    FILTER(ALL('Table'),[WeekIndex]<=0),[Inventory])
)

2. Result:

If the above results do not meet your expectations, you can express the expected results in the form of a picture, we can better help you!

Best Regards,

Liu Yang

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

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Expected result measure: =
SUMX (
    VALUES ( Data[Year-Week] ),
    CALCULATE (
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Data[WeekIndex] ) <= 0, [Inventory measure:],
            SELECTEDVALUE ( Data[WeekIndex] ) > 0, [Inventory measure:] - [Planned Sales measure:]
        )
    )
)