Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
For each GL_Account field (100,101 etc) i need to take first and last Posting_Date Row and copy Opening and Closing value to new columns Opening_New and Closing_New . These Posting_Date values will vary as per Date Slicer Selection.
Example- > For GL_Account - 100
First Posting_Date is 10-Apr-2018 so i need to Copy Opening value(1000) to Opening_New column
Last Posting_Date is 11-Apr-2018 so i need to Copy Closing value(4000) to Closing_New column
For GL_Account - 101
First Posting_Date is 10-Apr-2018 so i need to Copy Opening value(6000) to Opening_New column
Last Posting_Date is 12-Apr-2018 so i need to Copy Closing value(3000) to Closing_New column
Solved! Go to Solution.
Hi @Anonymous,
There are many approaches. Since something isn't clear about the data, I created a solid solution. Please download the demo from the attachment.
1. Add an index in the Query Editor.
2. Create two measures.
Opening_New = VAR minIndex = CALCULATE ( MIN ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( MIN ( Table1[Index] ) = minIndex, CALCULATE ( SUM ( Table1[Opening] ), Table1[Index] = minIndex ), 0 )
Closing_New = VAR maxIndex = CALCULATE ( MAX ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( MIN ( Table1[Index] ) = maxIndex, CALCULATE ( SUM ( Table1[Closing] ), Table1[Index] = maxIndex ), 0 )
Best Regards,
Hi @Anonymous,
There are many approaches. Since something isn't clear about the data, I created a solid solution. Please download the demo from the attachment.
1. Add an index in the Query Editor.
2. Create two measures.
Opening_New = VAR minIndex = CALCULATE ( MIN ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( MIN ( Table1[Index] ) = minIndex, CALCULATE ( SUM ( Table1[Opening] ), Table1[Index] = minIndex ), 0 )
Closing_New = VAR maxIndex = CALCULATE ( MAX ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( MIN ( Table1[Index] ) = maxIndex, CALCULATE ( SUM ( Table1[Closing] ), Table1[Index] = maxIndex ), 0 )
Best Regards,
Hi,
How to show the actual sum of the last 2 columns
i.e , Opening_New = 1000 + 6000 = 7000
Closing_New = 4000 +3000 = 7000
Hi @Anonymous ,
Try these two new measures, please.
Opening_New_1 = VAR minOuterIndex = CALCULATE ( MIN ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( HASONEVALUE ( Table1[Posting_Date] ), IF ( MIN ( Table1[Index] ) = minOuterIndex, CALCULATE ( SUM ( Table1[Opening] ), Table1[Index] = minOuterIndex ), 0 ), SUMX ( 'Table1', VAR minIndex = CALCULATE ( MIN ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( Table1[Index] = minIndex, CALCULATE ( SUM ( Table1[Opening] ), Table1[Index] = minIndex ), 0 ) ) )
Closing_New_1 = VAR maxOuterIndex = CALCULATE ( MAX ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( HASONEVALUE ( Table1[Posting_Date] ), IF ( MIN ( Table1[Index] ) = maxOuterIndex, CALCULATE ( SUM ( Table1[Closing] ), Table1[Index] = maxOuterIndex ), 0 ), SUMX ( 'Table1', VAR maxIndex = CALCULATE ( MAX ( Table1[Index] ), ALLSELECTED ( Table1[Posting_Date] ), ALLEXCEPT ( Table1, Table1[GL_Account], Table1[Posting_Date] ) ) RETURN IF ( Table1[Index] = maxIndex, CALCULATE ( SUM ( Table1[Closing] ), Table1[Index] = maxIndex ), 0 ) ) )
Best Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |