cancel
Showing results for
Did you mean:
Highlighted
Member

## Calculate the durations (days took from version 1 to verion n)

Hello All,

I have a data like date(field) and versions(field). Each customer can have 0.1 version to 2.0 respected dates to that verisions like 0.1 have date and 0.7 version will have another date. So each versions have dates.

For example,

1) if cutomer1 have versions from 0.1, 0.5, 1.0, 1.2 .and their respected dates are 10-07-2017, 13-07-2017, 16-07-2017, 20-07-2017. So now i have to calculate days between 4 versions.

2) if cutomer2 have versions from 0.1, 0.5 .and their respected dates are 10-07-2017, 13-07-2017. So now i have to calculate days between 2 versions.

For better understanding, i am attaching an image. please check below,

I am very new to PowerBi.. please Sugegst me how can i achieve this.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculate the durations (days took from version 1 to verion n)

Hey,

here you will find a litlte pbix file.

Within the table I created two calculated column, "Is latest Version" and "Version Age". "Is latest Version" flags the row with the latest Date for each customer

```Is Latest Version =
var currentDate = 'Table1'[Date]
return
IF(
CALCULATE(
MAX('Table1'[Date])
,ALLEXCEPT('Table1', Table1[Customer])
)=currentDate, "TRUE","FALSE")```

and "Version Age", that calculates the Age in days between the current date and the date of the first version for each customer. Be aware that this column can not be aggregated using SUM(...).

```Version Age =
var currentDate = 'Table1'[Date]
var startDate =
CALCULATE(
MIN('Table1'[Date])
,ALLEXCEPT('Table1', Table1[Customer])
)
return
DATEDIFF(startDate, currentDate, DAY)```

Here is a little screenshot from the report

Hope this gets you started

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
3 REPLIES 3
Super User

## Re: Calculate the durations (days took from version 1 to verion n)

Hey,

here you will find a litlte pbix file.

Within the table I created two calculated column, "Is latest Version" and "Version Age". "Is latest Version" flags the row with the latest Date for each customer

```Is Latest Version =
var currentDate = 'Table1'[Date]
return
IF(
CALCULATE(
MAX('Table1'[Date])
,ALLEXCEPT('Table1', Table1[Customer])
)=currentDate, "TRUE","FALSE")```

and "Version Age", that calculates the Age in days between the current date and the date of the first version for each customer. Be aware that this column can not be aggregated using SUM(...).

```Version Age =
var currentDate = 'Table1'[Date]
var startDate =
CALCULATE(
MIN('Table1'[Date])
,ALLEXCEPT('Table1', Table1[Customer])
)
return
DATEDIFF(startDate, currentDate, DAY)```

Here is a little screenshot from the report

Hope this gets you started

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Member

## Re: Calculate the durations (days took from version 1 to verion n)

Hello @TomMartens

Thank you. But, i want to know the duration days.. i mean if version 0.1 is 20-07-2017 and version n is 28-07-2017.

I want to show total days took 28 days to reach version n.

Super User

## Re: Calculate the durations (days took from version 1 to verion n)

Hey,

I'm wondering how you calculate the duration of 28 days in your excample

version 0.1 2017-07-20

version n 2017-07-28

In my logic this would result in a duration of  8 days, one can argue that this duration is 9 instead of 8

In my example above looking at customer 1

it starts with verion 1 on 2017-07-12 and version 2 is reached on 2017-07-15, for this reason an age of 3 (or 4) is calculated.

Using this example what would be the logic to calculate the duration?

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!