Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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))
@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))
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |