cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mehaboob557 Member
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,

test.PNG

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
Date - Age between Dates in Groups.png

 

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
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
Date - Age between Dates in Groups.png

 

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!
mehaboob557 Member
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.

 

I want this type of senario. Can u please help me in this way ?

Super User
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!