DAX - Date filter by slicer not working

[ki855]

Can somebody please help with this issue 

I need to filter my sales and stock table with date slicer 

so here is my SQL Query: 

DECLARE @DATE1 DATETIME

DECLARE @DATE2 DATETIME

SET @DATE1='2019-04-01'

SET @DATE2='2019-04-30'

select dt1.LOC_ID, dt1.ITEM_ID, dt1.QTY SalesQty, dt2.QTY StockQty, dt3.QTY ROS

from (

        SELECT LOC_ID, ITEM_ID, SUM(QTY) QTY

        FROM vw_TR_Sales

        WHERE TRANS_DATE BETWEEN @DATE1 AND @DATE2

        GROUP BY LOC_ID, ITEM_ID

    ) dt1 left join

    (

        SELECT LOC_ID, ITEM_ID, SUM(QTY) QTY

        FROM vw_TR_Stock

        WHERE TRANS_DATE <= @DATE2

        GROUP BY LOC_ID, ITEM_ID

    ) dt2 ON dt1.LOC_ID=dt2.LOC_ID AND dt1.ITEM_ID=dt2.ITEM_ID

      left join

    (

              SELECT LOC_ID, ITEM_ID, SUM(QTY)/4.0 QTY

        FROM vw_TR_Sales

        WHERE TRANS_DATE BETWEEN @DATE2-28 AND @DATE2

        GROUP BY LOC_ID, ITEM_ID

    ) dt3 ON dt1.LOC_ID=dt3.LOC_ID AND dt1.ITEM_ID=dt3.ITEM_ID

I have tried with DAX

Sales_Summary = ADDCOLUMNS(

SUMMARIZE(_TR_Sales, _TR_Sales[Item_Loc]),

"Sales_QTY", CALCULATE(SUM(_TR_Sales[QTY]), VALUES(_TR_Sales[TRANS_DATE]) ))

Not working 

[v-chuncz-msft]

@ki855 ,

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Apply-date-slicer-to-GROUPBY-table/td-p/459577

[ki855]

Thanks for help 

but I really need to write DAX for filtering my Sales table and stock table with 1 slicer 

For sales table it should work as is 

But for stock table it  should work from beginning of history

 like I wrote on SQL QUERY above

[MikeJohnsonZA · Accepted solution]

You can overwrite the current date selection using the CALCULATE function then use the DATESBETWEEN function to specify a new date range

It's not clear if you are using a date table but if you are the calculation could look something like this

Stock Level =
VAR StartDate =
    DATE ( 1900, 1, 1 )
VAR EndDate =
    MAX ( 'Date'[Date] )
VAR TotalStock =
    CALCULATE (
        [Stock QTY] ,
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
RETURN
    TotalStock

#this code is not tested so may contain typos

[ki855]

Thanks for help 

[ki855]

Here is the link for pbix 

If anyone want to test 

https://lawreps-my.sharepoint.com/:u:/g/personal/ki855_nig3_a1p_me/EUWou2_QHClFgi3a7vIecyMBRfc3aSryC...