Date range in Direct Query mode

[Anonymous]

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

[v-yiruan-msft · Accepted 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

[lovishsood1]

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

[amitchandak]

@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