extract Last transaction of each month

[Anonymous]

hi, there are multiple transection on last date of the month and there is no time, i have to find balance at the end of each month, i need to extract the balance in last transetion of each month

[v-xicai]

Hi @Anonymous   ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

You may enter into Query Editor, go to Add Column -> Index Column, choose option "From 1". In this way, there will be an contiguous index for the table, and then use this new created index column instead of the original [Index] in formula of nandukrishnavs.

Best Regards,

Amy 

Community Support Team _ Amy

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

[Greg_Deckler]

@Anonymous If you need a flag column just use:

Flag = IF([Date] = EOMONTH([Date],0),1,0)

Otherwise, you could use a variation of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434 

[amitchandak]

@Anonymous ,

Try closingbalancemonth

closingbalancemonth(Sum(Table[Month wise outstanding]), Table[Date])

prefer to use with date table

closingbalancemonth(Sum(Table[Month wise outstanding]), Date[Date])

refer video: how to use https://youtu.be/yPQ9UV37LOU

[amitchandak]

@Anonymous , I think, both I and  @nandukrishnavs  has given measure and you are trying as column

new flag column

if([Transaction Date] =eomonth([Transaction Date] ,0),1,0)

[Anonymous]

 @nandukrishnavs  has given me new column solution that i want, but there's some thing wrong in my formula as i didn't get "Last Transection" all i get is "NO"

[nandukrishnavs]

@Anonymous 

I tried to replicate your scenario using dummy data.

Transaction Date	Balance	Index
30-04-2020	200	1
01-05-2020	240	2
02-05-2020	280	3
28-05-2020	320	4
29-05-2020	360	5
30-05-2020	400	6
31-05-2020	440	7
31-05-2020	480	8
01-06-2020	520	9
02-06-2020	560	10
29-06-2020	600	11
30-06-2020	640	12
30-06-2020	680	13

I have created a calculated column. That will help us to identify the last transaction row.

LastTransaction = 
VAR _LastDay =
    EOMONTH ( 'Table'[Transaction Date], 0 )
VAR _MaxIndexInCurrentMonth =
    MAXX (
        FILTER (
            'Table',
            AND (
                MONTH ( 'Table'[Transaction Date] ) = MONTH ( _LastDay ),
                YEAR ( 'Table'[Transaction Date] ) = YEAR ( _LastDay )
            )
        ),
        'Table'[Index]
    )
VAR _Result =
    IF (
        AND ( 'Table'[Transaction Date] = _LastDay, 'Table'[Index] = _MaxIndexInCurrentMonth ),
        "Last Transaction",
        "No"
    )
RETURN
    _Result

[Anonymous]

hi i appreciate your reply, i applied it but it appears only "No"

can you tell me whats wrong in my formula

LastTransaction =

VAR _LastDay =

EOMONTH ( 'General Ledger'[Transaction Date], 0 )

VAR _MaxIndexInCurrentMonth =

MAXX (

FILTER (

'General Ledger',

AND (

MONTH ( 'General Ledger'[Transaction Date] ) = MONTH ( _LastDay ),

YEAR ( 'General Ledger'[Transaction Date] ) = YEAR ( _LastDay )

)

),

'General Ledger'[Index]

)

VAR _Result =

IF (

AND ( 'General Ledger'[Transaction Date] = _LastDay, 'General Ledger'[Index] = _MaxIndexInCurrentMonth ),

"Last Transaction",

"No"

)

RETURN

_Result

[nandukrishnavs]

@Anonymous 

Try this, I have replaced the function EOMONTH() as ENDOFMONTH()

LastTransaction =
VAR _LastDay =
    ENDOFMONTH ( 'General Ledger'[Transaction Date] )
VAR _MaxIndexInCurrentMonth =
    MAXX (
        FILTER (
            'General Ledger',
            AND (
                MONTH ( 'General Ledger'[Transaction Date] ) = MONTH ( _LastDay ),
                YEAR ( 'General Ledger'[Transaction Date] ) = YEAR ( _LastDay )
            )
        ),
        'General Ledger'[Index]
    )
VAR _Result =
    IF (
        AND (
            'General Ledger'[Transaction Date] = _LastDay,
            'General Ledger'[Index] = _MaxIndexInCurrentMonth
        ),
        "Last Transaction",
        "No"
    )
RETURN
    _Result

[Sebastien_BI]

Hello, your solution only returns the last 12 end of month.  Is it possible to extend so that all end of months from previous years are returned?

last value LASTDATE = 
var _LastDay = EOMONTH('Bank'[End of Month],0)
var _MaxIndexInCurrentMonth = MAXX(FILTER('Bank',AND(MONTH(Bank'[End of Month]) = MONTH(_LastDay),
YEAR(('Bank'[End of Month]) = YEAR(_LastDay))
)
),
'Bank'[Index])
var _Result = IF(AND('Bank'[End of Month] = _LastDay,'Bank'[Index] = _MaxIndexInCurrentMonth),"Last Transaction","No")
Return
_Result

[Anonymous]

@nandukrishnavs still all i get is "NO"

[nandukrishnavs]

@Anonymous 

To debug the problem, Could you try the formula suggested by @Greg_Deckler  and see what flag output you are getting.

Flag =
IF (
    'General Ledger'[Transaction Date]
        = EOMONTH ( 'General Ledger'[Transaction Date], 0 ),
    1,
    0
)

Flag =
IF (
    'General Ledger'[Transaction Date]
        = ENDOFMONTH ( 'General Ledger'[Transaction Date] ),
    1,
    0
)

[Anonymous]

@nandukrishnavs yes but it flaged all transection of last date, i just need to flag last transection on last date, i find your solution more appropriate if it works fine

[nandukrishnavs]

@Anonymous 

Ok, Now return the variable _MaxIndexInCurrentMonth  instead of _Result and see whether you are getting the correct index value or not. We are expecting the highest index value in that particular month.

[Anonymous]

not getting right index, 

[nandukrishnavs]

@Anonymous 

Can you share the snapshot and mention what is the expected index value.