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

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?