topic Create a measure to compare two dates and return result in a number format in DAX Commands and Tips

Create a measure to compare two dates and return result in a number format

Elisa112 — Thu, 12 Oct 2023 14:19:54 GMT

Hi Community

I need some help creating a measure to compare two dates in an engagement table and return a number in the correct format. The strange thing is I have created the measure for another table however when I imported it to a new dasboard the format came back as number(date) format.

Here is my measure formula which should calculate the number of working days between two dates in the table:

WorkingDaysMtg = NETWORKDAYS(SELECTEDVALUE('AllEngagements'[Start Date]), SELECTEDVALUE('AllMeetings'[Meeting Date]))

Which returns the result:

32284 ( I am expecting a single digit such as 0,1,2,3 or 4 etc)

I have tried to format the result via a number of different ways but nothing works. I'm just getting back into PBI after a some time away so i am a little bit rusty.

Any help much appreciated!

Thanks

Elisa

Re: Create a measure to compare two dates and return result in a number format

DallasBaba — Thu, 12 Oct 2023 15:12:05 GMT

Elisa112 you can modify your measure or custom column like :

WorkingDaysMtgFormatted =
VAR WorkingDays = NETWORKDAYS(SELECTEDVALUE('AllEngagements'[Start Date]), SELECTEDVALUE('AllMeetings'[Meeting Date]))
RETURN
IF(WorkingDays < 5, WorkingDays, 4)

Let me know if this works

Thanks

Re: Create a measure to compare two dates and return result in a number format

Elisa112 — Mon, 16 Oct 2023 12:23:53 GMT

Hi DallasBaba

thank you, this only brings back "4" for each result which is incorrect, as I am looking to find the number of working days between two meeting dates for each engagement. what i really need to format my original measure. It is currently returning a date value in a numerical format e.g. 32280

and I am looking for the number (1,2,3,4...10 etc).

Any assistance welcome

Re: Create a measure to compare two dates and return result in a number format

DallasBaba — Mon, 16 Oct 2023 18:27:11 GMT

Elisa112 Can you try the following measure to return the number of working days:

WorkingDaysMtg = NETWORKDAYS(‘AllEngagements’[Start Date], ‘AllMeetings’[Meeting Date])

==========OR
WorkingDaysMtg =
VAR StartDate = SELECTEDVALUE('AllEngagements'[Start Date])
VAR MeetingDate = SELECTEDVALUE('AllMeetings'[Meeting Date])
VAR WorkingDays = NETWORKDAYS(StartDate, MeetingDate)
RETURN
WorkingDays

This should return the correct working days for each engagement and meeting date pair. You can also format the measure as a whole number in the Modeling tab.

Let me know if this works.
Thanks

Re: Create a measure to compare two dates and return result in a number format

Elisa112 — Wed, 18 Oct 2023 08:57:31 GMT

Hi DallasBaba

thanks so much for you help, I have now managed to pull through the correct data type

Thanks again!

Re: Create a measure to compare two dates and return result in a number format

DallasBaba — Wed, 18 Oct 2023 16:30:42 GMT

Elisa112 Thanks for let me you you got the problem slove

Please mark my post as a solution, I answer your question!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Thanks

Re: Create a measure to compare two dates and return result in a number format

Elisa112 — Thu, 14 Dec 2023 16:42:24 GMT

Hi again

Unfortnately I had to resolve another error and your solution did not work after that. I will try to repost the question.

thanks