DAX calculation - SUM sales the latest 2 days

Question

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:

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:

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:

Can some of you please help!

Thanks. It is greatly appreciated.

Br, Jakob

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-11-22T01:52:49

Assuming there is a date dimension table named "Calendar", this worked for me.

=IF(
    COUNTROWS(
        INTERSECT(
            VALUES('Calendar'[Date]),
            CALCULATETABLE(VALUES(Table[Date]), ALL(Table))
        )
    ) > 0,
    CALCULATE(
        SUM(Table[Sale]),
        CALCULATETABLE(
            TOPN(2, SUMMARIZE(Table, Table[Date]), Table[Date], DESC),
            FILTER(ALL(Table), Table[Date] <= MAX('Calendar'[Date]))
        ),
        ALL('Calendar')
    )
)

DAX calculation - SUM sales the latest 2 days

1 Answers