Fetch First and Last Date Value based on Date Slicer Selection

[Anonymous]

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
        

[v-jiascu-msft · Accepted 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,

[Anonymous]

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]

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,