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,
I am having an issue with finding the revenue, because some rows are not populated with the exchange rate number (because it is only entered on random dates).
So I have a long list of orders in Query 1(in US dollars) and Query 2 has exchange rates (to convert to CAD).
Query 1 looks like this:
And Query 2 looks like this. It has exchange rates and the random dates they were entered into the system.
For example, the first exchange rate listed (1.223) applies to all dates between 10/1/2019 and 11/6/2020, so until a new exchange rate is entered in the system.
I am trying to calculate revenue (Revenue = Unit Price * Quantity * Exchange Rate) but since there is not an exchange rate for every day, only the day that the number was entered, I am having difficulties.
Thank you!
Solved! Go to Solution.
Hi @Anonymous
Kindly check below results:
Pbix attached.
Hi @Anonymous
Use the below measures:
Measure 2 = IF(ISBLANK(MINX(FILTER(ALL(Table_4),[Date]>MAX(Table_4[Date])),[Date])),DATE(9999,12,31),MINX(FILTER(ALL(Table_4),[Date]>MAX(Table_4[Date])),[Date]))
Measure 3 = MINX(FILTER('Table_4',MAX(Table_3[Date])>=[Date]&&MAX(Table_3[Date])<[Measure 2]),[Exchange Rate])
Revenue1 = MAX(Table_3[Quantity])*MAX(Table_3[UnitPrice])*[Measure 3]
Is there any way to do this in direct query?
Currently, I can not use MINX, EARLIER, or FILTER in the column.
Hi @Anonymous
Use the below measures:
Measure 2 = IF(ISBLANK(MINX(FILTER(ALL(Table_4),[Date]>MAX(Table_4[Date])),[Date])),DATE(9999,12,31),MINX(FILTER(ALL(Table_4),[Date]>MAX(Table_4[Date])),[Date]))
Measure 3 = MINX(FILTER('Table_4',MAX(Table_3[Date])>=[Date]&&MAX(Table_3[Date])<[Measure 2]),[Exchange Rate])
Revenue1 = MAX(Table_3[Quantity])*MAX(Table_3[UnitPrice])*[Measure 3]
@Anonymous - Seems like you will need to create a measure to return the maximum date for the exchange rate that is less than the current date in your row. You can do this with MAXX using the correct FILTER. You could then use that to again use MAXX and FILTER to lookup the corresponding exchange rate. See this pattern:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |