Forum Discussion
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
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
3 Replies
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 LinkedinHi 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
