opening stock

[christianjire]

LastDateInPreviousMonth =

VAR SelectedDate = MAX('TT sellout 2023 to date'[Date])

VAR PreviousMonthStartDate = EOMONTH(SelectedDate, -2) + 1

VAR PreviousMonthEndDate = EOMONTH(SelectedDate, -1)

RETURN

    CALCULATE(

        MAX('TT sellout 2023 to date'[Date]),

        FILTER(

            ALL('TT sellout 2023 to date'),  -- Use ALL to ensure all data is considered

            'TT sellout 2023 to date'[Date] >= PreviousMonthStartDate &&

            'TT sellout 2023 to date'[Date] <= PreviousMonthEndDate

        )

    )

Opening Stock Previous Month =

VAR LastDateInPrevMonth = [LastDateInPreviousMonth]

RETURN

    CALCULATE(

        SUM('TT sellout 2023 to date'[Inventory in Cases]),

        FILTER(

            ALLSELECTED('TT sellout 2023 to date'),

            'TT sellout 2023 to date'[Date] = LastDateInPrevMonth

        )

    )

i have this two measures that check the last date of each prevoius month for inventory in cases and displays the sum as the opening stock. the problem comes in when i select one month on the slicer it shows the data as blank but if i choose multiple months for example Jan to may it shows the correct data. Does anyone know how to solve this?

[v-yangliu-msft · Accepted solution]

Hi  @christianjire ,

You might consider using the SELECTEDVALUE() function to get the specific product or a specific category:

Opening Stock Previous Month =
VAR LastDateInPrevMonth = [LastDateInPreviousMonth]
VAR SelectProduct=SELECTEDVALUE('TT sellout 2023 to date'[Product])
VAR SelectCategory=SELECTEDVALUE('TT sellout 2023 to date'[category])
RETURN
    CALCULATE(
        SUM('TT sellout 2023 to date'[Inventory in Cases]),
        FILTER(
            ALL('TT sellout 2023 to date'),
            'TT sellout 2023 to date'[Date] = LastDateInPrevMonth&&
            'TT sellout 2023 to date'[Product]=SelectProduct &&
            'TT sellout 2023 to date'[category]=SelectCategory
        )
    )

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.

[v-yangliu-msft]

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

You can change ALLSELECTED() to All() for that formula:

Opening Stock Previous Month =
VAR LastDateInPrevMonth = [LastDateInPreviousMonth]
RETURN
    CALCULATE(
        SUM('TT sellout 2023 to date'[Inventory in Cases]),
        FILTER(
            ALL('TT sellout 2023 to date'),
            'TT sellout 2023 to date'[Date] = LastDateInPrevMonth
        )
    )

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.

[christianjire]

Thanks Liu, i had tried that and it works. the issue it that i would like for other slicers to affect it but it doesn't work. for example if i want to view stock for a specific product or a specific category, the slicers do not affect it at all.

[lbendlin]

Your slicer must be fed from a disconnected table.