Forum Discussion
Filter Stock with date
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 :
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
1 Reply
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