Forum Discussion

Divya904's avatar
Divya904
Helper III
8 years ago

How to get difference between Fact table Date and User Input Date?

Hi Experts

 

Please help me to get the difference between Order date (fact table that) and Calendar date (User input date through slicer. This calendar has been generated by using DAX ) I have created the DAX measure as below, which is not working fine.

 

DifferenceOfPostingAndInputDate = DATEDIFF(FIRSTDATE('Sales'[Posting Date ]), FIRSTDATE('Date'[Calendar Date]),DAY )

 

FYI : I have already created a relationship between fact table and calender table.

 

Thanks in advance.

Need Help

7 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi Divya904,

     Please help me to get the difference between Order date (fact table that) and Calendar date (User input date through slicer. This calendar has been generated by using DAX ) I have created the DAX measure as below, which is not working fine.

     

    DifferenceOfPostingAndInputDate = DATEDIFF(FIRSTDATE('Sales'[Posting Date ]), FIRSTDATE('Date'[Calendar Date]),DAY )

     

    FYI : I have already created a relationship between fact table and calender table.

     

     

    Could you show the screenshot why the formula does not work fine? And what is your expected output.

     

    If it is convenient, could you share some data sample which could reproduce your scenario, so that we can help further investigate on it?

     

    Best Regards,

    Cherry

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Do you get a different result when you use this formula?

     

    =MIN('Date'[Calendar Date])-MIN('Sales'[Posting Date ])

    • Divya904's avatar
      Divya904
      Helper III
      8 years ago
      Hi Ashish
      Thanks for your help. Yes, I get different result when I use
      =MIN('Date'[Posting date])-MIN('Sales'[User selected date in filter])

      Or

      =Max('Date'[Posting date])-Max('Sales'[User selected date in slicer ])

      OR

      =FIRSTDATE('Date'[Calendar Date])-FIRSTDATE('Sales'[Posting Date ])

      I don’t know which function I should use here to subtract UserInput date in slicer and PostingDate.
      There are several functions.
      I have also tried with Datediff function as:
      Number of days(measure) = Datediff(MIN('Date'[Posting Date]),MIN('Sales'[User selected date in slicer, day])

      Thanks n regards