Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |