Filter Stock with date

[Anonymous]

Hello everyone ! 

I did a SQL query to get stock of my articles and documents affecting the stock (purchase order and supplier). However I need to know the stock on a given date, when I use the power bi filter, this one directly removes the items if there is no document on my date.

Have you some idea what i can do ? 

Example with AUSONNE - 63.1.152SC PAR 10/BOITE : 

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Suppose the original table structure is similar to below:

As there is no corresponding record for AUSONNE - 63.1.152SC PAR 10/BOITE on '2017-01-01' and '2017-01-02', if we choose date range between '2017-01-03' and '2017-01-04' from slicer, of course, it will remove the items if there is no document (here is AUSONNE - 63.1.152SC PAR 10/BOITE).

To work around this, we can try to create a new calculated table in two steps.

Stock_1 =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( Stock[DOC_DT_PRV] ), VALUES ( Stock[LIG_LIB] ) ),
    "Stock", 0
)

Stock_2 = UNION(Stock,Stock_1)

 

Then, drag corresponding fileds from 'Stock_2' into Matrix and slicer.

However, if your table structure is like below, you can first Pivot it in Query Editor mode, in order to get a new structure same as above. Alternatively, if you don't want to pivot table, you only need to make a little adjustment to above formulas, the logic is the same. Please refer to the .pbix file for more details.

Best regards,
Yuliana Gu