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

[Divya904]

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.

[Ashish_Mathur]

Hi,

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

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

[Divya904]

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

[Ashish_Mathur]

Which one of them gives you the correct answer?

[Divya904]

Hi Ashish
I am not getting the required output. I am also not sure which function is 100% correct to use here. Once I am sure which function I should use, then I can check other table calculations to see if there is any issue with those.
I am just wondering is User selected date in slicer being passed to the function or not?

Thanks

[Divya904]

I am not getting the required output. I am also not sure which function is 100% correct to use here. Once I am sure which function I should use, then I can check other table calculations to see if there is any issue with those.
Thanks

[Ashish_Mathur]

Hi,

The one i proposed should give you the correct answer.

[v-piga-msft]

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