Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 i...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

amitchandak's avatar
amitchandak
Super User
6 years ago

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