Dax calculation: Sum sale in the latest 2 days

[jayjay0306]

Hi People,

I need your help.

I have a table (“Table”) like this.

In the table below I have SUM “sales” by the LATEST 2 days with sale (not the latest 2 DATES! i.e. example: if the latest sales update is on a Tuesday, it sums the sale for Monday and Friday (no sale in weekend)) for each products.

in other words (in red):

The calculation is made with the following DAX calculated column:

Sale last 2 days=
VAR ProductDates =
CALCULATETABLE (
        VALUES ( Table[Date]),
        ALLEXCEPT ( Table, Table[Product_ID])
    )
VAR LastTwoDates = TOPN ( 2; ProductDates;[Date] )
RETURN
    CALCULATE (
        SUM ([Sale]);
        ALLEXCEPT ( Table, Table[Product_ID] );
        Table[Date] IN LastTwoDates)

Now, I need to take it a step further:

What I want to do is to make a new calculations which SUM the sale for each product for the latest 2 days, but ONLY for the Distributors, where the "Distributor indicator"=1. And the latest 2 sales days in question, are the sales days where there has been sale to these distributors only.

(example: if the latest sales day is a tuesday and there were no sale from these distributors yesterday, the the latest two days will be previous friday and thursday (i.e. the latest 2 days where sales is not null). 

I know I can use the calculation, I have already made, but I can’t figure out where to put the logic in, in order to get the right result:

Example:

Can some of you please help!

Thanks. It is greatly appreciated.

Br,

Jakob

[Anonymous · Accepted solution]

@jayjay0306  please create a calculated column as per below. 

Column = 
VAR _prevdate = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[Product_ID]),'Table'[date]<MAX('Table'[date])))
RETURN CALCULATE(SUM('Table'[Sales]),FILTER(ALLEXCEPT('Table','Table'[Product_ID]),'Table'[date]>=_prevdate && 'Table'[Distributor Indicator] = 1))