calculate with date

[Nelleke-NL]

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 [start] and [end] and placed that in new column

Sometimes [end] is not filled, then today's date.
This way I can show the average turnaround time for closed files and ongoing files

Is it simple or .... 

[Nelleke-NL]

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]

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]

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 op	Dossiernummer	Startdatum	Einddatum
7-1-2022	1	7-1-2022	9-1-2023
5-1-2022	2	5-1-2022	1-2-2022
12-1-2022	3	12-1-2022	19-1-2022
12-1-2022	4	12-1-2022	20-4-2022
13-1-2022	5	15-1-2022	24-8-2022
14-1-2022	6	15-1-2022	19-1-2022
14-1-2022	7	14-1-2022	16-5-2022
27-6-2013	8	27-6-2013	24-4-2019
20-1-2022	9	21-1-2022	1-2-2022
21-1-2022	10	21-1-2022	1-2-2022
24-1-2022	11	24-1-2022	26-1-2022
25-1-2022	12	26-1-2022	3-7-2023
25-1-2022	13	26-1-2022	22-3-2022
28-1-2022	14	28-1-2022	24-5-2022
14-1-2022	15	14-1-2022	27-1-2022
16-11-2020	16	16-11-2020	24-11-2020
1-2-2022	17	1-2-2022
14-4-2021	18	14-4-2021	23-11-2021
17-3-2021	19	17-3-2021	17-3-2021
14-1-2020	20	14-1-2020	14-1-2020
14-2-2022	21	14-2-2022	14-3-2022
18-2-2022	22	18-2-2022	9-3-2022
18-2-2022	23	18-2-2022	16-7-2022
11-4-2022	24	11-4-2022	11-4-2022
23-2-2022	25	23-2-2022	25-9-2022
1-3-2022	26	4-3-2022	20-2-2023

[v-xinruzhu-msft · Accepted solution]

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]

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

[Nelleke-NL]

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?

[123abc]

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.