Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a Sales table with ProductKey, OrderDate and Total Sale. I also have a Date table related to Sales by OrderDate, the Date table contains a YearMonth column. How can I create a measure that returns the first Total Sale of each ProductKey on every YearMonth.
SALES
(d/m/yyyy)
ProductKey OrderDate Total Sales
1 1/1/2018 $100
1 2/1/2018 $120
1 2/1/2018 $110
2 2/1/2018 $130
2 2/1/2018 $400
3 3/1/2018 $200
3 4/1/2018 $225
3 1/2/2018 $230
3 2/2/2018 $210
DATE
(d/m/yyyy) YYYYMM
Date YearMonth
1/1/2018 201801
2/1/2018 201801
3/1/2018 201801
... ...
... ...
... ...
28/2/2018 201802
RESULTS (Expected) (SUM of Sales for the first sales date in a month by product)
ProductKey OrderDate Total Sales FirstSaleOfMonth
1 1/1/2018 $100 100
1 2/1/2018 $120 100
1 2/1/2018 $110 100
2 2/1/2018 $130 130
2 2/1/2018 $400 130
3 3/1/2018 $200 200
3 4/1/2018 $225 200
1 2/2/2018 $80 200
1 2/2/2018 $120 200
1 3/2/2018 $120 200
1 3/2/2018 $110 200
3 1/2/2018 $230 230
3 2/2/2018 $210 230
Thanks
can you post sample data of both tables?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |