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 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
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,
3 Replies
v-jiascu-msftMicrosoft Employee
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,
- v-jiascu-msft
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,