last 3 days sales

[Avinash19]

Hi,

I need help with a dax query.

I want to calculate last 3 days sales of a product in a particular store, so if there is a sale in last 3 days then it will display result as 1 but if there is no sale in last 3 days then result should be 0.

STORE

---------

date	product_id	store_id	cnt_txns	last3days sales
06/12/22	A1	12	1	0
07/12/22	A1	12	5	1(we can see a sale on 06/12/2022 for same product and store)
11/12/22	A1	12	0	0(no sale on 8,9,10,11)
06/12/22	B1	13	8	0
07/12/22	B1	13	1	1(we can see a sale on 06/12/2022 for same product and store)
07/12/22	C1	14	1	0

I'm using below dax to calculate the last 3 days sale on 11/12/2022, but it is returning only output for cnt_txns 0 ie product_id A1 and store_id 12. But it should also return Product B1,C1 since there were no txns for these 2 also for past 3 days.

sales last 3 days = var currdate = MAX(Calender[Date])

var dteinx = SELECTEDVALUE(store[Index])

return

IF(dteinx >1,

CALCULATE(sum(store[cnt_trx]),DATESINPERIOD(Calender[Date],currdate,-3,DAY)))

Can some please help me to understand what I'm doing wrong?

[v-yaningy-msft]

Hi, @Avinash19 

Thanks for @Greg_Deckler reply. Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

sales last 3 days = 
VAR _currdate =
    MAX ( 'Calendar'[date] )
VAR _result =
    CALCULATE (
        SUM ( store[cnt_trx] ),
        FILTER ( store, store[date] <= _currdate - 3 || store[date] > _currdate )
    )
RETURN
    IF ( NOT ISBLANK ( _result ), 0, 1 )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Avinash19]

Hi Yang,

thanks for your reply.

but the output should be like below.

STORE

---------

date	product_id	store_id	cnt_txns	last3days sales
06/12/22	A1	12	1	0
07/12/22	A1	12	5	1(we can see a sale on 06/12/2022 for same product and store)
11/12/22	A1	12	0	0(no sale on 8,9,10,11)
06/12/22	B1	13	8	0
07/12/22	B1	13	1	1(we can see a sale on 06/12/2022 for same product and store)
07/12/22	C1	14	1	0

[v-yaningy-msft · Accepted solution]

Hi, @Avinash19 

Please try following measure.

previousDate =
VAR _index =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Index] < SELECTEDVALUE ( 'Table'[Index] ) ),
        [Index]
    )
VAR _previous_date =
    CALCULATE (
        MAX ( 'Table'[date(m/d/y)] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = _index )
    )
VAR _previous_product_id =
    CALCULATE (
        MAX ( 'Table'[product_id] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = _index )
    )
VAR _previous_store_id =
    CALCULATE (
        MAX ( 'Table'[store_id] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = _index )
    )
VAR _date_diff =
    IF (
        NOT ISBLANK ( _previous_date ),
        CALCULATE ( INT ( SELECTEDVALUE ( 'Table'[date(m/d/y)] ) - _previous_date ) )
    )
VAR _isSameProductID =
    IF (
        ( NOT ISBLANK ( _previous_product_id ) )
            && SELECTEDVALUE ( 'Table'[product_id] ) = _previous_product_id,
        1
    )
VAR _isSameStoreID =
    IF (
        ( NOT ISBLANK ( _previous_store_id ) )
            && SELECTEDVALUE ( 'Table'[store_id] ) = _previous_store_id,
        1
    )
RETURN
    IF (
        _isSameProductID && _isSameStoreID
            && ( _date_diff >= 0
            && _date_diff <= 3 ),
        1,
        0
    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Greg_Deckler]

@Avinash19 Something going awry with CALCULATE and DAX TI functions. Avoid them:

sales last 3 days = var currdate = MAX(Calender[Date])

var dteinx = SELECTEDVALUE(store[Index])

return

IF(dteinx >1,

SUMX(FILTER('store', [date] >= __currdate - 3), [cnt_trx])

[Avinash19]

Hi Greg,

thanks for ur solution but its still returning the same output what i'm getting with my code. Its not returning other values.