Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 p...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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