Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jayjay0306
Helper III
Helper III

Dax calculation: Sum sale in the latest 2 days

Hi People,

 

I need your help.

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

picture 1.png

 

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):

picture 2.png

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:

picture 3.png

 

Can some of you please help!

 

Thanks. It is greatly appreciated.

 

Br,

Jakob

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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))


last2date.png

View solution in original post

1 REPLY 1
Anonymous
Not applicable

@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))


last2date.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors