Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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"])
Thanks in advance!
Solved! Go to Solution.
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
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
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())?
try null without quotas in Power query, just = null
do not hesitate to give a kudo to useful posts and mark solutions as solution
- 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!
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |