Forum Discussion

annejeel's avatar
annejeel
New Member
4 years ago
Solved

Difference between two dates without using DAX

Hello oracle. 

 

I am VERY new to Power BI and DAX is terrifying to me, so, please be gentle 🙂 

 

I want to work out the amount of time (in days) between two dates. I'd like to work this out for the single dates but also looks at averages across all of the dates. Is this possible?, and is it possible to do without using DAX as I'm completely clueless there? 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi  annejeel ,

    Pls test the below:（difference)

    = Table.AddColumn(#"Changed Type", "Custom", each let 
s = [date1],
e =[date2],
x =
List.Generate(
()=>s,
each _<=e,
each Date.AddDays(_,1)
),
l = List.Select(
x,
each Date.DayOfWeek(_,Day.Monday)<5
)
in 
List.Count(l))

    Average:

    = Table.AddColumn(#"Changed Type1", "Custom2", each List.Average(#"Changed Type1"[datediff]))

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


    Best Regards

    Lucien

2 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    4 years ago

    Hi

     if you want to find the difference between two dates then you can achieve it in Power Query editor like below. Share some sample data and also show the expected result to help more. 

     

    Thanks

    Hari

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi  annejeel ,

    Pls test the below:（difference)

    = Table.AddColumn(#"Changed Type", "Custom", each let 
s = [date1],
e =[date2],
x =
List.Generate(
()=>s,
each _<=e,
each Date.AddDays(_,1)
),
l = List.Select(
x,
each Date.DayOfWeek(_,Day.Monday)<5
)
in 
List.Count(l))

    Average:

    = Table.AddColumn(#"Changed Type1", "Custom2", each List.Average(#"Changed Type1"[datediff]))

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


    Best Regards

    Lucien