Calculate open days from Start Date

Power Query
1

Good day all,

Hope all is well, i need some guidance on the power query Editor side. I am trying to calculate open days from Start Date to end date. I have created the query but, anything with no end date returns “null”.

I would like to the null to be replaced with current date: not sure how to get this fixed/

image
image879×372 82 KB

Thanks for the help!

1 Like
2

Apologies forgot to add the .PBIX

Duration days.pbix (175.2 KB)

1 Like
3

Hi @neilonbooysen,

You didn’t include the data or supply a separate data file…

image
image878×131 8.25 KB

Create a new blank query and enter this code:
Date.From( DateTime.FixedLocalNow() )

Disable load and Rename it something like: Today

Add an if statement to your open days calculation and perform a logical test on the [End date] something like:
if ( [End date] = null and [Start date] <> null ) then Number.From( Today - [Start date]) else Number.From( [End date] - [Start date])

Replace the calculation with how you’d like to calculate that Day Difference
I hope this is helpful

1 Like
4

Hi Melissa ,

I apologize about that, file attached

Duration Days.csv (165.9 KB)
Duration days.pbix (175.2 KB)

Neilon

1 Like
5

Hi @neilonbooysen,

Your Start- and End dates are of datetime datatype so the Today query also needs to return a datetime value. In short, entering DateTime.FixedLocalNow() is enough because that returns a datetime datatype.

Next I amended the logic for your Duration Days custom column.

Number.RoundDown(
  Number.From(
    (if [End date] = null and [Start date] <> null then Today else [End date]) - [Start date]
  )
)

Here’s your file.
Duration days.pbix (195.4 KB)

I hope this is helpful.

1 Like
6

Thanks so much for this, much appreciated.

1 Like