Forum Discussion

mpalha04's avatar
mpalha04
Helper III
7 years ago
Solved

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...
Need Help
  • mpalha04's avatar
    mpalha04
    7 years ago

    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]))  )

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

On a simple dataset, share the expected result.  Also, share some data so that i can paste it in an Excel workbook.

mpalha04's avatar
mpalha04
Helper III
7 years ago

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!

  • kgova's avatar
    kgova
    Frequent Visitor
    7 years ago

    use below measure you will get the desired output

     

    Measure = CALCULATE(SUM(Table1[order total]),FILTER(Table1,Table1[order date] > DATEVALUE("4/4/2017"))  )

     

    • mpalha04's avatar
      mpalha04
      Helper III
      7 years ago

      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]))  )