cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AbhilashNair Frequent Visitor
Frequent Visitor

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
        
Capture1.JPG
Capture2.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Fetch First and Last Date Value based on Date Slicer Selection

Hi @AbhilashNair,

 

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
    )

Fetch-First-and-Last-Date-Value-based-on-Date-Slicer-Selection

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Fetch First and Last Date Value based on Date Slicer Selection

Hi @AbhilashNair,

 

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
    )

Fetch-First-and-Last-Date-Value-based-on-Date-Slicer-Selection

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AbhilashNair Frequent Visitor
Frequent Visitor

Re: Fetch First and Last Date Value based on Date Slicer Selection

Hi,

How to show the actual sum of the last 2 columns  
i.e , Opening_New = 1000 + 6000  = 7000
       Closing_New  =  4000 +3000  = 7000

tempsnip.png

Community Support Team
Community Support Team

Re: Fetch First and Last Date Value based on Date Slicer Selection

Hi @AbhilashNair ,

 

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
                )
        )
    )

Fetch-First-and-Last-Date-Value-based-on-Date-Slicer-Selection2

 

Best Regards,

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.