Forum Discussion

Akos9207's avatar
Akos9207
Frequent Visitor
3 years ago

Starting stock level

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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      Akos9207
      Frequent Visitor
      3 years ago

      Hi, 


      Thanks for the reply.

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

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        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 )