Difference Between dates in number of weeks

[T_Francis]

Hi, 

I'm trying to raise a simple report showing the number of weeks between two dates, I have not been able to find how to do it, could someone please help. 

Just a third column showing the number of weeks with 1 as a minimum is all i need.

Thank you

[FrankAT · Accepted solution]

Hi @T_Francis 

you can do it within Power Query like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBRCsAgDEPv4rfQtHVzO4t4/2usCs4q+BmeaRJLCZKIQQLmEIPiFzWWgIsgTcKYgSE6E8eECfdk8hJ4MPews+yZ+dTdVH9TV9/jfHsX9j0tPB0Y9Oxrxd6TL5+3b/uWDVvesl023/LXM7zWDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"HCT START DATE" = _t, #"HCT PLANNED END DATE" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HCT START DATE", type date}, {"HCT PLANNED END DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "# of Weeks", each Number.RoundUp(Number.From([HCT PLANNED END DATE] - [HCT START DATE]) / 7,0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"# of Weeks", Int64.Type}})
in
    #"Changed Type1"

Regards FrankAT

[AntrikshSharma]

( Date 1 - Date 2 ) / 7 ?

[T_Francis]

Thank you for responding but this way sends up an error message.

[az38]

@T_Francis 

it still looks like a Custom Column for Power Query interface.

[AntrikshSharma]

Use the transformation buttons from ribbon, see how I did it.

let
    Source = Excel.CurrentWorkbook(){[Name = "Table23"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {{"Date", type date}, {"Other Date", type date}}
    ),
    #"Inserted Date Subtraction" = Table.AddColumn(
        #"Changed Type", 
        "Subtraction", 
        each Duration.Days([Other Date] - [Date]), 
        Int64.Type
    ),
    #"Divided Column" = Table.TransformColumns(
        #"Inserted Date Subtraction", 
        {{"Subtraction", each _ / 7, type number}}
    ),
    #"Rounded Up" = Table.TransformColumns(
        #"Divided Column", 
        {{"Subtraction", Number.RoundUp, Int64.Type}}
    )
in
    #"Rounded Up"

[jthomson]

What the error would be would be useful, that it just says error isn't overly helpful

If you're wanting it to show 1 week for 1-7 days, 2 weeks for 8-14 days etc, then this really is a simple case of finding the difference in days, dividing by 7, then using Number.RoundUp (if using Power Query) or ROUNDUP (in DAX)

[parry2k]

@T_Francis you can add new column using dax

Number of Weeks = MIN ( DATEDIFF ( Table[Start Date], Table[End Dat], WEEK ), 1 )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[T_Francis]

Thank you, 

The formula does not seem to be working

am i making an error somewhere?

I need to use the result on a spreadsheet, so is there a way to do it on power query or do i have to create a measure?

[AntrikshSharma]

Yes what he wrote is DAX code and you are writing M code.