Forum Discussion

Nelleke-NL's avatar
Nelleke-NL
Helper II
2 years ago
Solved

calculate with date

I have found a lot of complex calculating in DAX, but much to difficult for me. I'm just a beginner...   In my data I have 2 date colums [start] and [end]. I want to calculate the months between [...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Nelleke-NL 

    You can create a custom cloumn and input the following code

    =if [Einddatum]<>null then Number.Round(Number.From(([Einddatum] -[Startdatum])/( 365.25 / 12 )),0) else Number.Round(Number.From((DateTime.Date(DateTime.LocalNow()) -[Startdatum])/( 365.25 / 12 )),0)

    Output

    and you can create a blank query and put the whole code in advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZNJjsQgDEWv0mIdJGxCgLNEdf9rtIcQPqirF5E8vHj4wH2HGilyYg5HIPnA7W7m8DnuUGZcP3DFcFMx4pnImgSf+rB38txITvEEMs9MURJnOWMD8pyZayO37kBWzYJPVyyT5Bov8Ui3adoRfOmuc1J3MoF2mqUvIi2J9C+JY5F3nLtfuBHjrmtajiLWeZQLmTeSOWYo2gDVU8KAzFL+VpTKJinXRXzpZ24ytV3yGdEdH8foIYhgFQU6ws/obLdF9aHmnd8A56cYOVt9PWO7lYTAa+NCNlKChVb7Qd+ZvBIExAZFqQFqaQj0r2TeSBGsAkrjvah6dldmYNp2ohmqFhcIArFD1TGN3hB9peAn/0ku1OcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Gemaakt op" = _t, Dossiernummer = _t, Startdatum = _t, Einddatum = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Gemaakt op", type text}, {"Dossiernummer", Int64.Type}, {"Startdatum", type text}, {"Einddatum", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Gemaakt op", type date}, {"Startdatum", type date}, {"Einddatum", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each if [Einddatum]<>null then Number.Round(Number.From(([Einddatum] -[Startdatum])/( 365.25 / 12 )),0) else Number.Round(Number.From((DateTime.Date(DateTime.LocalNow()) -[Startdatum])/( 365.25 / 12 )),0))
in
    #"Added Custom"

     

    Best Regards!

    Yolo Zhu

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

123abc's avatar
123abc
Community Champion
2 years ago

Certainly, calculating the months between two dates and handling cases where the end date might be empty (assuming you mean null or blank, and you want to consider today's date in that case) is a common task in data analysis. If you're using DAX (Data Analysis Expressions), you can create a new column in your table to store the calculated months. Here's a simple example to get you started:

Assuming your table is named YourTable and your date columns are named [start] and [end], and you want a new column called [TurnaroundMonths], you can use the following DAX formula:

 

TurnaroundMonths =
IF(
ISBLANK(YourTable[end]),
DATEDIFF(YourTable[start], TODAY(), MONTH),
DATEDIFF(YourTable[start], YourTable[end], MONTH)
)

 

This formula checks if the [end] column is blank. If it is, it calculates the difference between [start] and today's date using DATEDIFF. If [end] is not blank, it calculates the difference between [start] and [end].

This is a simplified example, and you might need to adjust it based on your specific requirements or the structure of your data. If you have more complex scenarios or special cases, feel free to provide additional details, and I can help you refine the formula.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.