Starting stock level

[Akos9207]

Hi,

I would like to ask for some help with the following problem.

Sample data

ItemID  Stock   Date                 ID

1           10        2022.12.12.     1
1           14        2022.12.14      2
1            7         2022.12.14      3
1           18        2022.12.16      4

I have a Date filter (user can change it) and a matrix. I would like to see the starting stock level, based on the date filter (min date). If there are more records on the same date, the last ID decide the correct stock level.

Expected result:

Date filter set to 2022.12.12 - 2022.12.16 --> Stock 10
Date filter set to 2022.12.14 - 2022.12.14 --> Stock 7 (same date, different ID)
Date filter set to 2022.12.15 - 2022.12.16 --> Stock 7 (No record on 2022.12.15 so the closest date to it is 2022.12.14)

I tried this topn to get the correct stock but its not working 😕

MAXX(
TOPN (
1,
FILTER(view_PBI_BTK,MIN(view_PBI_BTK[date])-1),
view_PBI_BTK[date], ASC,view_PBI_BTK[Id],DESC
),
view_PBI_BTK[stock]
)

Thank you.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: =
VAR _mindateslicer =
    MIN ( 'Calendar'[Date] )
VAR _maxdatedata =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[ItemID] = MAX ( Data[ItemID] )
                && Data[Date] <= _mindateslicer
        ),
        Data[Date]
    )
RETURN
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[ID]
                = MAXX (
                    GROUPBY (
                        FILTER (
                            ALL ( Data ),
                            Data[ItemID] = MAX ( Data[ItemID] )
                                && Data[Date] = _maxdatedata
                        ),
                        "@maxID", MAXX ( CURRENTGROUP (), Data[ID] )
                    ),
                    [@maxID]
                )
        ),
        Data[Stock]
    )

[Akos9207]

Hi, 

Thanks for the reply.

I tried this solution but with my ~60 million record number, unfortunately it doesnt even calculate anything 😞

[Jihwan_Kim]

Hi,

Thank you for your feedback, and please try the below whether it shows any result and it suits your requirement.

Expected result measure: =
VAR _mindateslicer =
    MIN ( 'Calendar'[Date] )
VAR _maxdatedata =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[ItemID] = MAX ( Data[ItemID] )
                && Data[Date] <= _mindateslicer
        ),
        Data[Date]
    )
VAR _maxid =
    MAXX (
        GROUPBY (
            FILTER (
                ALL ( Data ),
                Data[ItemID] = MAX ( Data[ItemID] )
                    && Data[Date] = _maxdatedata
            ),
            "@maxID", MAXX ( CURRENTGROUP (), Data[ID] )
        ),
        [@maxID]
    )
RETURN
    CALCULATE ( MAX ( Data[Stock] ), REMOVEFILTERS ( calendar ), Data[ID] = _maxid )

[Akos9207]

Hi,

Thanks for the reply. I tried this measure and got mixed results.

The good: For some products i got the correct result.

The bad: The calculation time is very slow with full matrix , and in some cases i didnt even get any result.

I used the TopN function to get the end stock quantity and it works great. If that could be change somehow to see record outside of the date filter, but keep other filters would be nice... if possible...