topic Re: opening stock in DAX Commands and Tips

opening stock

Anonymous — Tue, 10 Sep 2024 20:59:09 GMT

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?

Re: opening stock

lbendlin — Tue, 10 Sep 2024 23:48:32 GMT

Your slicer must be fed from a disconnected table.

Re: opening stock

Anonymous — Wed, 11 Sep 2024 07:49:22 GMT

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.

Re: opening stock

Anonymous — Wed, 11 Sep 2024 09:19:22 GMT

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.

Re: opening stock

Anonymous — Fri, 13 Sep 2024 07:47:14 GMT

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.