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

[Elisa112]

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

[DallasBaba]

@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

[Elisa112]

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

[DallasBaba · Accepted solution]

@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

[Elisa112]

Hi @DallasBaba 

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

Thanks again!

[DallasBaba]

@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

[Elisa112]

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