Forum Discussion
Transpose table with calculation
CornelisV This will calculate the number of minutes in a column. See attached PBIX below sig.
Column = VAR __Phase = [Phase] VAR __Current = [Date time] VAR __Type = SWITCH( TRUE(), __Phase = "End heating", "Start heating", __Phase = "End cooling", "Start Cooling" ) VAR __Result = SWITCH( __Phase, "Start heating", 0, "Start cooling", 0, VAR __DateTime = [Date time] VAR __Result = MAXX( FILTER( ALL( 'Table' ), [Date time] < __DateTime && [Phase] = __Type ), [Date time] ) RETURN __Current - __Result ) RETURN __Result * 60 * 24
Hi,CornelisV .Thank you for your reply.
During my testing, the date column was not really put into the calculation logic, it was in my case a reference column to generate the index. The most important logic is that the current row selected is calculated against the previous row if it is an odd number, as you mentioned, creating an index column as a marker to determine the order of calculation is the most important point, in my test, determining whether the index is odd or not is the key element, it doesn't matter whether the two rows of data being calculated are on the same day or not.
In my tests, determining whether the index is odd or not is the key element. You tried labeling the two rows of data before and after with a single index, which is also a viable solution (two rows of data with the same index are computed).
All in all, creating the right labeled columns is the key to solving your problem.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Carson Jian,
Thank you for your explanation.
I agree with you and your demonstrated steps gives me a lot of inspiration for other some issues in the Power BI.
It is not very easy for me, since I'm trying to apply process data calculation, that is different in comparison to financial data treatment.
Best regards,
Cornelis