topic Re: Last value of each bank account per year & month in DAX Commands and Tips

Last value of each bank account per year & month

MoFiMo — Wed, 22 Dec 2021 18:14:08 GMT

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 😉

Re: Last value of each bank account per year & month

bcdobbs — Wed, 22 Dec 2021 19:26:39 GMT

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?

Re: Last value of each bank account per year & month

AlexisOlson — Wed, 22 Dec 2021 19:58:46 GMT

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 )

Re: Last value of each bank account per year & month

MoFiMo — Thu, 23 Dec 2021 07:45:54 GMT

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 😄

Re: Last value of each bank account per year & month

Whitewater100 — Thu, 23 Dec 2021 22:27:33 GMT

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.],

Re: Last value of each bank account per year & month

Whitewater100 — Thu, 23 Dec 2021 22:28:14 GMT

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

Re: Last value of each bank account per year & month

V-lianl-msft — Mon, 27 Dec 2021 07:10:35 GMT

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.