Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |