Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MahamoodBi
Frequent Visitor

Need to find date difference between two different tables in power bi

Hi PBI Experts,

 

I have Table 1 and Table2 data in Table1 i have a ''Service date'' Column and Table2 i have Termdate column ,

MahamoodBi_0-1661918820035.png

 

 

i need to findout date difference between (Table1)Servicedate and (Table2)Termdate by Month after that i need to divided by Datediff / 12 

My output will like below screenshot

MahamoodBi_1-1661918864629.png

 

Could you please help.

 

6 REPLIES 6
mohammedadnant
Impactful Individual
Impactful Individual

Hi MahamoodBi

 

you can merge these 2 tables into 1 in Power Query (merge tables using empid)

in DAX calculated column

DATEDIFF (DATE1, DATE2, MONTH) /12
Thanks & Regards,

Mohammed Adnan

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18
SergioSilvaPT
Resolver V
Resolver V

@MahamoodBi 

 

Check your solution in DAX:

 

SergioSilvaPT_0-1661965899394.png

 

Output =
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE(
        'Table 1',
        'Table 1'[Empid],
        'Table 1'[status],
        'Table 1'[Servicedate]
        ),
        "Action", LOOKUPVALUE('Table 2'[Action],'Table 2'[Empid],'Table 1'[Empid]),
        "Date Diff", DATEDIFF( [Servicedate], LOOKUPVALUE('Table 2'[Termdate],'Table 2'[Empid],'Table 1'[Empid]),MONTH)
    ),
       "DateDiff/12", DIVIDE([Date Diff] , 12,0)
)

 

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

@SergioSilvaPT 

i am getting error after created the dax

MahamoodBi_0-1661970726452.png

 

@MahamoodBi you're trying to create a measure with a DAX code that results a table.

 

SergioSilvaPT_1-1662020789127.png

Instead of New measure choose New table and copy the DAX code.

 

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

 

Regards,
Sérgio Silva

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/
Vijay_A_Verma
Super User
Super User

I have created a solution in Excel based Power Query which you can download from https://1drv.ms/x/s!Akd5y6ruJhvhujd-m0li-RwWbFUS?e=hB3NG1 

This is the output query

let
    Source = Table.NestedJoin(Table1, {"EmpID"}, Table2, {"EmpID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Action", "Termdate"}, {"Action", "Termdate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "DateDiff", each Number.RoundDown(Duration.Days([Termdate]-[Servicedate])/(365/12),0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DateDiff/12", each [DateDiff]/12)
in
    #"Added Custom1"

@Vijay_A_Verma i need power bi dax function to find out solution.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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