Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |