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.

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

Thank you but ... formule is no problem but I get an error on the IF function

screen

If I only use DATEDIFF(YourTable[start], YourTable[end], MONTH)

then error on DATEDIFF ;(

Please...

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

I apologize for any confusion. It seems that the DATEDIFF function might not be available in your version of Power BI, or there might be a syntax issue. Let's try an alternative approach using the following formula:

 

MonthsBetween =
IF(
ISBLANK([end]),
INT(DATEDIFF([start], TODAY(), DAY) / 30.44),
INT(DATEDIFF([start], [end], DAY) / 30.44)
)

 

This formula uses the DATEDIFF function to calculate the difference in days and then divides that by the average number of days in a month (30.44) to get the difference in months.

Make sure to replace [start] and [end] with the actual names of your date columns.

If you continue to face issues or if you have specific error messages, please provide more details so that I can assist you further.

 

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.

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

    No is not working. sorry for language

     

    My language is dutch I get a yellow meassge  error in IF name

    I have the version Power BI on desktop, no licences

    Gemaakt opDossiernummerStartdatumEinddatum
    7-1-202217-1-20229-1-2023
    5-1-202225-1-20221-2-2022
    12-1-2022312-1-202219-1-2022
    12-1-2022412-1-202220-4-2022
    13-1-2022515-1-202224-8-2022
    14-1-2022615-1-202219-1-2022
    14-1-2022714-1-202216-5-2022
    27-6-2013827-6-201324-4-2019
    20-1-2022921-1-20221-2-2022
    21-1-20221021-1-20221-2-2022
    24-1-20221124-1-202226-1-2022
    25-1-20221226-1-20223-7-2023
    25-1-20221326-1-202222-3-2022
    28-1-20221428-1-202224-5-2022
    14-1-20221514-1-202227-1-2022
    16-11-20201616-11-202024-11-2020
    1-2-2022171-2-2022 
    14-4-20211814-4-202123-11-2021
    17-3-20211917-3-202117-3-2021
    14-1-20202014-1-202014-1-2020
    14-2-20222114-2-202214-3-2022
    18-2-20222218-2-20229-3-2022
    18-2-20222318-2-202216-7-2022
    11-4-20222411-4-202211-4-2022
    23-2-20222523-2-202225-9-2022
    1-3-2022264-3-202220-2-2023
    • Anonymous's avatar
      Anonymous
      Not applicable
      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.

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

        Great! It works. I'm happy so happy. Thats gives us a lot more information. Thanks💐