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

     

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