Help with query to summarize stock data

[dkozul]

Hello,

I am new to DAX so i need help.

I have table with stock data that is filled when stock quantity changes for example:

The logic should be like this:

- Take everything from that table where date is lower or equal to selected date.

- Group by PharmacyId and ProductId and for every group take quantity with biggest date

- Sum quantites from that result table.

Like you see in my example for some dates i have data for some i don't. For that that i don't it should take last avaliable.

I wrote two mesasures:

1. StockCountRows - first just returns number of rows from table that i calculate to see if that part is correct:

    COUNTROWS(CALCULATETABLE(StockItemTest; FILTER(ALL(DimVrijeme); DimVrijeme[Datum] <= MAX(DimVrijeme[Datum]))))

2. StockQuantity - second is the main measure which contains the first one:

SUMX(

    ADDCOLUMNS(

        SUMMARIZE(

            CALCULATETABLE(StockItemTest; FILTER(ALL(DimVrijeme); DimVrijeme[Datum] <= MAX(DimVrijeme[Datum])));

            StockItemTest[ProductId];

            StockItemTest[PharmacyId];

            "MaxDate"; MAX(StockItemTest[Date] )

        );

    "QuantityPerGroup"; CALCULATE(SELECTEDVALUE(StockItemTest[Quantity]); StockItemTest[Date] = EARLIER([MaxDate]))

    ); [QuantityPerGroup]

)

Everything is fine when i choose a month for which i have data, let's say September:

But when i choose August i get blank:

I need help because i don't know what to do next.

Thanks

[v-chuncz-msft · Accepted solution]

@dkozul 

You may change it as follows.

        "QuantityPerGroup", CALCULATE (
            SELECTEDVALUE ( StockItemTest[Quantity] ),
            StockItemTest[Date] = EARLIER ( [MaxDate] ),
            ALL ( DimVrijeme[Datum] )
        )

[dkozul]

Thank you.