Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nelleke-NL
Helper II
Helper II

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 [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 .... 

 

1 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

vxinruzhumsft_0-1700634471290.png

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.

View solution in original post

7 REPLIES 7
Nelleke-NL
Helper II
Helper II

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

screenscreen

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

then error on DATEDIFF ;(

Please...

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.

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

Schermafbeelding 2023-11-21 084108.png

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

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

vxinruzhumsft_0-1700634471290.png

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.

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

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors