Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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
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
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
@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
Connect on Linkedin
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
72 | |
70 | |
37 | |
29 | |
26 |
User | Count |
---|---|
91 | |
49 | |
44 | |
38 | |
37 |