Forum Discussion
Fetch First and Last Date Value based on Date Slicer Selection
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...
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
v-jiascu-msft
Microsoft Employee
Microsoft EmployeeHi 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,