Forum Discussion

Baarathi88's avatar
Baarathi88
Helper III
6 years ago
Solved

Multiple Date Mapping

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 

 

OrderIDOrderDateShippedDateDelivery DateSales
113/1/202015/02/202013/03/2020100
215/02/202013/03/202015/04/202050
215/02/202013/04/202015/05/2020300

Data Model

 
 
 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • nandic's avatar
    nandic
    6 years ago

    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's avatar
    richbenmintz
    6 years ago

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

     

3 Replies

  • nandic's avatar
    nandic
    Resident Rockstar
    6 years ago

    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's avatar
    richbenmintz
    Resident Rockstar
    6 years ago

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