Last value of each bank account per year & month

[MoFiMo]

Hello.

I have tried several things and maybe I am just thinking too complicated. Therefore I will ask you for support 😉

In general I have already a workaround in Excel, as I started long ago with a dashboard in Excel. But now I want to transfer it. I have the following situation:

In my table there are listed several accounts with several bookings on the same days, but not on each day in a month (like a normal bank account). 

Date	Account	Booking	End of day Value
25.01.2021	A	150	150
28.01.2021	B	5000	5000
28.01.2021	A	-50	100
27.02.2021	A	-60	40
28.02.2021	B	-500	3000
28.02.2021	B	-1500	3000
15.03.2021	A	360	400
21.03.2021	B	3200	6200
...
01.04.2021	A	-30	370

Now I would like to extract the "End of day value" for each account per month & year. But I cannot take the last day of each month like 31.01.2021, 28.02.2021 ... as it is not a must that there was a booking on those days.

Date	Account	End of day value
28.01.2021	A	100
28.01.2021	B	5000
27.02.2021	A	40
28.02.2021	B	3000
15.03.2021	A	400
21.03.2021	B	6200
....

I don't care if it's just a visual + filtering or if I create a DAX statement for a new table or measure.

Here my comparison of what I have (many data points) 

and what I want to achieve (just one data point for each month+year)

I hope you got my point. It's a little bit difficult to describe 😉

[V-lianl-msft]

Hi @MoFiMo ,

You can try to create a max date of monthly column:

Max_date = CALCULATE(MAX('Table'[Date]),FILTER('Table',EARLIER('Table'[Account])='Table'[Account]&&EARLIER('Table'[Date].[Year])='Table'[Date].[Year]&&EARLIER('Table'[Date].[MonthNo])='Table'[Date].[MonthNo]))

Then create a new table:

Table 2 = 
distinct(SELECTCOLUMNS(FILTER('Table','Table'[Date]='Table'[Max_date]),"account",'Table'[Account],"date",'Table'[Date],"end_of_value",'Table'[End of day Value]))

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

[Whitewater100]

Hello:

You can use this measure, I believe:

Last Balance =

CALCULATE( LASTNONBLANKVALUE(DISTINCT(Dates[Date]), [Value Amt])). I will try to attach the file so you can see the model. Hope this helps. Bill S.

You can get rid of the total with beginning with = IF(HASONEVALUE('Dates'[Month No.], 

[Whitewater100]

Sorry [Value Amt] is just the sum of the ending balances. 

[MoFiMo]

Thanks for your replies. It seems that it is more complicated than I thought.

As I haven't done so much with DAX until now, I will need to start from the beginning and then I will try your suggestions 😉

But I am still open for other proposals ...  there are always many different ways for problems like that 😄

[AlexisOlson]

You can create a calculated table like that by adding a month column to group by and then finding the maximal date for each account/month combination.

For example,

Table2 =
VAR AddMonth = ADDCOLUMNS ( Table1, "MonthEnd", EOMONTH ( Table1[Date], 0 ) )
VAR FilterDate =
    FILTER (
        AddMonth,
        Table1[Date]
            = MAXX (
                FILTER (
                    AddMonth,
                    Table1[Account] = EARLIER ( Table1[Account] )
                      && [MonthEnd] = EARLIER ( [MonthEnd] )
                ),
                Table1[Date]
            )
    )
RETURN
    SELECTCOLUMNS (
        FilterDate,
        "Date", Table1[Date],
        "End of day Value", Table1[End of day Value]
    )

I think it might be better as a measure though like this:

LastEoDValue = 
VAR CurrDay = MAX ( Table1[Date] )
VAR LastEoD = 
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            ALLSELECTED ( Table1[Date] ),
            EOMONTH ( Table1[Date], 0 ) = EOMONTH ( CurrDay, 0 )
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[End of day Value] ),
        Table1[Date] = LastEoD
    )

[bcdobbs]

I think you need some DAX here and to make use of LASTNONBLANK.

Have a read of https://www.sqlbi.com/articles/optimizing-lastnonblank-and-lastnonblankvalue-calculations/

Do you already have a date table in your model?