Forum Discussion
Calculation on Power BI Desktop like CELL on Excel
Hi,
Power BI Gurus, I need to do this on Power BI:
Excel example:
| Item | C | D | E |
| A | 05-01-2017 9:23 | 05-01-2017 16:31 | 16,5947222 |
| A | 06-01-2017 9:07 | 06-01-2017 10:17 | 39,0263889 |
| A | 08-01-2017 1:18 | 08-01-2017 4:07 | 12,3608333 |
| A | 08-01-2017 16:29 | 09-01-2017 8:00 | 0 |
Where:
E =(C3-D2)*24
I need to this dynamic in case I have to change the item, In the example also I have ordered "C" column from A-Z to get the difference.
Thanks.
Hi erickparra,
You can create calculated columns below:
RankC = RANKX(FILTER('Table1','Table1'[Item]=EARLIER(Table1[Item])),'Table1'[C],,ASC)
NextC = LOOKUPVALUE(Table1[C],'Table1'[Item],'Table1'[Item],'Table1'[RankC],'Table1'[RankC]+1)
E = DATEDIFF('Table1'[D],[NextC],DAY)*24
Best Regards,
Qiuyun YuHi,
Thanks for your time an answer, works perfect just I had to change the E:
E = ([NextC] - [D])*24
Because I have a lot of items I reviewed vs Excel and values were perfect.
Thanks a lot.
2 Replies
v-qiuyu-msftCommunity Support
Hi erickparra,
You can create calculated columns below:
RankC = RANKX(FILTER('Table1','Table1'[Item]=EARLIER(Table1[Item])),'Table1'[C],,ASC)
NextC = LOOKUPVALUE(Table1[C],'Table1'[Item],'Table1'[Item],'Table1'[RankC],'Table1'[RankC]+1)
E = DATEDIFF('Table1'[D],[NextC],DAY)*24
Best Regards,
Qiuyun YuHi,
Thanks for your time an answer, works perfect just I had to change the E:
E = ([NextC] - [D])*24
Because I have a lot of items I reviewed vs Excel and values were perfect.
Thanks a lot.