Multiple Date Mapping

[Baarathi88]

I have a data set with three date fields OrderDate, ShippedDate and DeliveryDate. I want to calculate sales in a Card Visual. 

Card 1 : Sales based on Order Date

Card 2 : Sales based on Shipped Date

Card 3 : Sales based on Delivery Date

Since I have mapped OrderDate with my MasterDates table, I'm getting sales based on OrderDate. Is there way to achieve the scenario based on below image.

The below is data i have 

OrderID	OrderDate	ShippedDate	Delivery Date	Sales
1	13/1/2020	15/02/2020	13/03/2020	100
2	15/02/2020	13/03/2020	15/04/2020	50
2	15/02/2020	13/04/2020	15/05/2020	300

Data Model

[nandic · Accepted solution]

Hi @Baarathi88 ,
You will need to use dax function UseRelationship for this purpose. This function allows you to use inactive join between tables to create calculation based on different days.
This link will help you: https://docs.microsoft.com/en-us/dax/userelationship-function-dax

Example 1st measure= CALCULATE(SUM(InternetSales[SalesAmount])
Example 2nd measure - inactive relationship: = CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))

Cheers,
Nemanja

[richbenmintz · Accepted solution]

@Baarathi88,

you will need to create three measures,

like 

order_date_sales = sum('Sheet1'[Sales])

ship_date_sales = calculate(sum('Sheet1'[Sales]), userelationship('Sheet1'[ShippedDate], Dates[Date]))
delivery_date_sales = calculate(sum('Sheet1'[Sales]), userelationship('Sheet1'[Delivery Date], Dates[Date]))

[Baarathi88]

Thank You. That works

[nandic · Accepted solution]

Hi @Baarathi88 ,
You will need to use dax function UseRelationship for this purpose. This function allows you to use inactive join between tables to create calculation based on different days.
This link will help you: https://docs.microsoft.com/en-us/dax/userelationship-function-dax

Example 1st measure= CALCULATE(SUM(InternetSales[SalesAmount])
Example 2nd measure - inactive relationship: = CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))

Cheers,
Nemanja