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

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💐

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

        I hardly dare to ask...
        December 31 is always a reference date. How can I use that date for separate column?

        Replace DateTime.LocalNow() with (Date(12/31/2023))

        I can now only find these figures on January 1, while it may only be later that I retrieve this data.

        I will then have to adjust the year each time. Do you have another solution for me?