cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Last value of each bank account per year & month

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 😉

6 REPLIES 6
Community Support

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.

Solution Sage

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

Solution Sage

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

Regular Visitor

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 😄

Super User

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 (
Table1[Date]
= MAXX (
FILTER (
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
)``````
Super User

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

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors