Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MoFiMo
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). 

 

DateAccountBookingEnd of day Value
25.01.2021A150150
28.01.2021B50005000
28.01.2021A-50100
27.02.2021A

-60

40
28.02.2021B-5003000
28.02.2021B-15003000
15.03.2021A

360

400
21.03.2021B32006200
...   
01.04.2021A-30370

 

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.

 

DateAccountEnd of day value
28.01.2021A100
28.01.2021B5000
27.02.2021A40
28.02.2021B3000
15.03.2021A400
21.03.2021B6200
....  

 

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) 

MoFiMo_1-1640196758977.png

 

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

MoFiMo_0-1640196728388.png

 

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

 

6 REPLIES 6
V-lianl-msft
Community Support
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]))

Vlianlmsft_0-1640589017477.png

 


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
Solution Sage
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.], 
Whitewater100_0-1640298328629.png

 

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

MoFiMo
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 😄

AlexisOlson
Super User
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 (
        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
Super User
Super User

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?



Ben Dobbs

LinkedIn | Twitter | Blog

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.