cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Date range in Direct Query mode

Hey guys,

I have two dates in two different tables and I need to get days between those two dates. Datediff function does not work, also it is not possible to create a column, because they are in different tables, the only way I think of is through creating measures for those dates and use measure calculation, but I am not sure what function needs to be used.

Do you guys have better ideas?

I am using direct query mode btw 🙂

Thanks,

Sona

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ，

You can create one measure as below,  please find full details in my sample PBIX file.

1. Get the value of the date columns in that two tables separately

2. Calculate the number of days between these two dates

GetDateRange =

VAR a =

MAX ( '001_t1'[ID] )VAR sdate =

CALCULATE ( MAX ( '001_t1'[Start date] ), '001_t1'[ID] = a )

VAR edate =

CALCULATE (

MAX ( '001_t2'[End Date] ),

FILTER ( '001_t2', '001_t2'[PID] = a )

)

VAR Ddiff =

DATEDIFF ( sdate, edate, DAY )

RETURN

Ddiff

If the above formula is not applicable in your scenario, please provide me the related table structure and sample data.

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

Hi @Anonymous ，

You can create one measure as below,  please find full details in my sample PBIX file.

1. Get the value of the date columns in that two tables separately

2. Calculate the number of days between these two dates

GetDateRange =

VAR a =

MAX ( '001_t1'[ID] )VAR sdate =

CALCULATE ( MAX ( '001_t1'[Start date] ), '001_t1'[ID] = a )

VAR edate =

CALCULATE (

MAX ( '001_t2'[End Date] ),

FILTER ( '001_t2', '001_t2'[PID] = a )

)

VAR Ddiff =

DATEDIFF ( sdate, edate, DAY )

RETURN

Ddiff

If the above formula is not applicable in your scenario, please provide me the related table structure and sample data.

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Hi,
I have the same query but instead of the count of days, I want to list all the dates between two dates(if exist).

Eg.:

Start Date = Feb,5,2022         End Date = Feb 8,2022

Output : Feb 5,2022

Feb 6,2022

Feb 7,2022

Feb 8,2022

Super User

@Anonymous

Few days back I created this pbix, the 4 ways you can take datediff between two tables. I am not using a direct query, so not sure the solution will work. But solutions 3 and 4 use measure approach so you can try that. (way 3, way 4)

https://www.dropbox.com/s/bs6m71k27gc76vc/datediff.pbix?dl=0Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors