Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

opening stock

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?
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi  Anonymous ,

     

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi  Anonymous ,

     

    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.