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,
I want to calculate total revenue for clients starting from different ship dates. The client ID and corresponding ship date is from an Excel table and I created a relationship to my orders table based on store ID. I also created a seperate date table to some time intelligence calculations. Please refer below:
For example, Hants East Rural High will start calculating total revenue from August 28.
Any help is appreciated! Thanks!
Solved! Go to Solution.
Thanks @kgova, @Ashish_Mathur,
Simple fix I should have figured. Solution below:
Measure = CALCULATE(SUM(Table1[order total]),FILTER(Table1,Table1[order date] > Table2 [ship date])) )
Hi,
On a simple dataset, share the expected result. Also, share some data so that i can paste it in an Excel workbook.
Hi @Ashish_Mathur,
Thanks for your reply. Please see below.
An example of order table:
An example of a Ship Date table, which consists of the store id and the store's respective ship date. I have this data in a seperate Excel table.
Expected result below when the date for the report is after APRIL 4:
If the date of the report is after MARCH 1, the expected result is below:
I have created a seperate date table.
Essentially, the sum of revenue should be running starting from each store's respective ship date. If the date is filtered before the store's ship date, revenue should be $0.
Thanks!
use below measure you will get the desired output
Measure = CALCULATE(SUM(Table1[order total]),FILTER(Table1,Table1[order date] > DATEVALUE("4/4/2017")) )
Thanks @kgova, @Ashish_Mathur,
Simple fix I should have figured. Solution below:
Measure = CALCULATE(SUM(Table1[order total]),FILTER(Table1,Table1[order date] > Table2 [ship date])) )
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 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |