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.
Hi There,
I have got two tables. Table A has Date and Quantity Column as bellow:
Date | Qty |
01-Jun-15 | 12246443 |
02-Jun-15 | 12737355 |
03-Jun-15 | 11901529 |
04-Jun-15 | 11574494 |
05-Jun-15 | 13583459 |
06-Jun-15 | 11980839 |
07-Jun-15 | 11727211 |
And Table B has bellow Rate Table:
Date | Rate |
01-Jun-15 | $0.015 |
05-Jun-15 | $0.016 |
10-Jun-15 | $0.017 |
Now how do i join them so that i can calculate the revenue?
Thanks,
Sami
Solved! Go to Solution.
There is no way to delete replies here!? My answer was... not exactly on target 😞
You need a way to calculate the "most recent rate". That is the last date in rate table, which is less than (or equal?) to the date in your quantities table.
Maybe something like... ?
=CALCULATE(
LASTNONBLANK(Rates[Date], MIN(Rates[Rate]),
FILTER(Rates, Rates[Date] <= MAX(Quantites[Date]))
)
Is your pricing a change schedule? For instance the price is $0.015 for any orders placed between 01-Jun-15 and 04-Jun-15, at which the rate changes to $0.016 for the next period? We need a little more information to help you out properly.
You will want to create a separate Calendar/Date table (that can have nice columns like Year-Month, etc). You will create relationships from the Calendar[DateKey] to each of the data columns in your data/fact tables shown.
When building reports, you will use columns from the Calendar table, which will magically filter BOTH of the tables you have here.
There is no way to delete replies here!? My answer was... not exactly on target 😞
You need a way to calculate the "most recent rate". That is the last date in rate table, which is less than (or equal?) to the date in your quantities table.
Maybe something like... ?
=CALCULATE(
LASTNONBLANK(Rates[Date], MIN(Rates[Rate]),
FILTER(Rates, Rates[Date] <= MAX(Quantites[Date]))
)
Sorry for the late reply but thanks, i had had it solved with kinda same solution.
Thanks,
Sami
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |