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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mehaboob557
Resolver IV
Resolver IV

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 ?

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.