Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello every one
I want to calculate the qty and sales for every item base on last lower price date until today and the calculation filtered by every branch as the following
itemid | lastlowerpricedate | oldprice | newprice | sales qty | sales | branch |
01 | 01-01-2023 | 100 | 50 | 10 | 500 | 01 |
02 | 22-03-2023 | 70 | 30 | 22 | 660 | 02 |
Note : the seles should start claculate from the lastlowerpricedate for each item and ignore any sales before the lastlowerpricedate
I have these tables
ITem Table
itemid | name |
01 | toy |
02 | t-shirt |
lowerpricetabel
item | lower price date | old price | newprice | qty | branch |
01 | 01-01-2023 | 100 | 50 | 23 | 01 |
02 | 01-02-2023 | 120 | 70 | 222 | 02 |
02 | 22-03-2023 | 70 | 30 | 27 | 01 |
sales Table
itemid | sales | qty | date | branch |
01 | 200 | 5 | 01-02-2023 | 01 |
01 | 300 | 5 | 13-02-2023 | 01 |
02 | 660 | 22 | 01-04-2023 | 02 |
Solved! Go to Solution.
Thank you @johnt75 for your reply
I used this measure which I got from microsoft being chat
Sales = CALCULATE(SUM(Sales[sales]),
FILTER(Sales, Sales[Itemid] = MAX(lowerpricetable[itemid]) &&
Sales[branch] = MAX(lowerpricetable[branch]) &&
Sales[Date] >= MAX(lowerpricetable[date])))
it works with me fine .
thanks
Try
Sales since lower price date =
VAR SummaryTable =
ADDCOLUMNS (
VALUES ( 'items'[item id] ),
"@lower price date", CALCULATE ( MAX ( 'lower price table'[lower price date] ) )
)
VAR Result =
SUMX (
SummaryTable,
CALCULATE (
SUM ( sales[sales] ),
DATESBETWEEN ( 'Date'[Date], [@lower price date], TODAY () )
)
)
RETURN
Result
Thank you @johnt75 for your reply
I used this measure which I got from microsoft being chat
Sales = CALCULATE(SUM(Sales[sales]),
FILTER(Sales, Sales[Itemid] = MAX(lowerpricetable[itemid]) &&
Sales[branch] = MAX(lowerpricetable[branch]) &&
Sales[Date] >= MAX(lowerpricetable[date])))
it works with me fine .
thanks
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |