cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
niklasgrundel
Regular Visitor

Need help to measure days between to dates in a column, with a twist

Hi, 

 

I need help to calculate days between column A and column B, the result should be presented in column C. I´ve managed to calculate days between A and B if they both contain dates. The twist is that I wan´t to use present day and calculate with order day if delivery date is missing. So column C should always present data.

I´m not really sure if this can be accomplished using Dax...

 

I´ve tried this code in a Power Query but it won´t work:

if ['Delivery date']=""

then (DateTime.Date-['Order date'])

else

(['Delivery Date']-[#"Order date"])

 

pbi_measure_days.jpg

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Need help to measure days between to dates in a column, with a twist

Hi @niklasgrundel 

In Power query you can create a custom column

= if [Delivery Date]="" or [Delivery Date]=null 
then Date.From(DateTime.LocalNow())-[Order date]
else [Delivery Date]-[Order date]

 

In DaX you can create a calculated column:

Column = 
IF(ISBLANK([Delivery Date]), DATEDIFF([Order Date], TODAY(), DAY), DATEDIFF([Order Date], [Delivery Date], DAY) )

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

5 REPLIES 5
Super User II
Super User II

Re: Need help to measure days between to dates in a column, with a twist

Hi @niklasgrundel 

In Power query you can create a custom column

= if [Delivery Date]="" or [Delivery Date]=null 
then Date.From(DateTime.LocalNow())-[Order date]
else [Delivery Date]-[Order date]

 

In DaX you can create a calculated column:

Column = 
IF(ISBLANK([Delivery Date]), DATEDIFF([Order Date], TODAY(), DAY), DATEDIFF([Order Date], [Delivery Date], DAY) )

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Highlighted
niklasgrundel
Regular Visitor

Re: Need help to measure days between to dates in a column, with a twist

Thanks @az38 for quick reply!

 

I manged to get you DaX-code to work as you can see below, the Power query won´t show and present days where the delivery date column is missing data, can it be a problem with Date.From(DateTime.LocalNow())?

 

DaxCode.jpg

 

 

 

 

 

PQcode.jpg

Super User II
Super User II

Re: Need help to measure days between to dates in a column, with a twist

Hi @niklasgrundel 

try null without quotas in Power query, just = null

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

niklasgrundel
Regular Visitor

Re: Need help to measure days between to dates in a column, with a twist

- To quote or note to quote, that´s the question!? 😉

 

When I removed the quotation of null your Power Query script worked aswell.

 

Thanks for your patience and help!

Super User II
Super User II

Re: Need help to measure days between to dates in a column, with a twist

There is no question, if I see null 🙂

Good luck!

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.