Forum Discussion
Filter Measure Based on Different Dates
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!
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])) )
4 Replies
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")) )
