Calculate the number of stock counts

[Justin_PowerBI]

Hi All, 

Thanks in advance for your help.

I have the following dataset: 

Store-Number    | Article-ID      | Date  (Year-Week)      | Stock Value | 

1000                        1                   2023-01    50 

1000                        2                   2023-01    75 

1000                        1                   2023-02    50

1000                        2                   2023-02    75

1001                        1                   2023-02    50

1001                        2                   2023-02    75

1001                        1                   2023-02    55

1001                        2                   2023-02    80

Stores are supposed to do a stock count every week. We're trying to determine whether the stores checked their total stock value each week (counted/priced all articles) based on the following logic:

Reasoning: 

- If for one store-number, any of the article_id's changed in Stock Value, compared to the week before. 

Expected Answer:

- In the example above, no stock-value changes happened for store 1000. We expect stock-count = 0 for that week.

   For store 1001 we see that at least 1 item changed in price (therefore we want stock count = 1) for that week. 

In total we should be able to determine the number of counts during a year, and divide that with the total number of years to get a percentage.

If any more info is needed, please let me know. 

[tamerj1]

Hi @Justin_PowerBI 
It is not so clear at total level if a store will be counted as many times as the weeks that have change or only once. The following counts for every week change.

Count =
COUNTROWS (
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Store-Number], 'Table'[Date (Year-Week)] ),
        VAR CurrentWeek = 'Table'[Date (Year-Week)]
        RETURN
            COUNTROWS (
                CALCULATETABLE (
                    VALUES ( 'Table'[Stock Value] ),
                    TOPN (
                        2,
                        FILTER (
                            ALL ( 'Table'[Date (Year-Week)] ),
                            'Table'[Date (Year-Week)] <= CurrentWeek
                        )
                    ),
                    ALL ( 'Table' )
                )
            ) = 2
    )
)