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....
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
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 )

     

    • Akos9207's avatar
      Akos9207
      Frequent Visitor
      3 years ago

      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...