Month End Balances per Account and Classification

[BLAP33]

Hello,

I currently have a dataset that auto-updates (sometimes daily) with a "Balance History" by account.  I have multiple columns labeled as "Account", "Class (EG Asset vs Liability)", "Date", and "Balance".  The date column is formatted as xx/xx/xxxx xx:xx:xx AM/PM.  Sometimes the same date will appear with a more recent time stamp.  My overall goal is to only grab rows by month end showing an ending balance per account.

I easily took my date/time column and converted it to a decimal date to provide a number based on how "recent" the data is.  I also added a MONTH integer column so I can easily see entries by the month per account.  I now need a formula to only populate a column if it is the latest balance for each account at the last monthly entry so I can chart assets vs liabilities on a month to month.  Example data below:

Does anyone know of DAX formatting to help out with this?

Date	Account	Institution	Balance	Type	Class
4/2/20 6:23 AM	Personal	BROKER	$500.00	INDIVIDUAL	Asset
4/2/20 6:23 AM	RTH_BL	BROKER	$505.00	ROTH_IRA	Asset
4/2/20 6:23 AM	HEALTH	BROKER	$510.00	HEALTH_SAVINGS_ACCOUNT_HSA	Asset
4/2/20 12:30 AM	BANK1	BANK1	$515.00	CHECKING	Asset
4/2/20 12:30 AM	VACATION	BANK1	$520.00	SAVINGS	Asset
4/2/20 12:30 AM	SAVINGS	BANK1	$525.00	SAVINGS	Asset
4/1/20 9:54 PM	VISA	BANK2	$530.00	OTHER	Liability
4/1/20 9:53 PM	SAVING2	BANK2	$535.00	SAVINGS	Asset
4/1/20 9:53 PM	CHECKING	BANK2	$540.00	CHECKING	Asset
3/31/20 5:32 AM	VISA	BANK2	$545.00	OTHER	Liability
3/31/20 5:32 AM	SAVING2	BANK2	$550.00	SAVINGS	Asset
3/31/20 5:32 AM	CHECKING	BANK2	$555.00	CHECKING	Asset
3/30/20 12:15 AM	VISA	BANK2	$560.00	OTHER	Liability
3/30/20 12:15 AM	CHECKING	BANK2	$565.00	CHECKING	Asset
3/30/20 12:15 AM	SAVING2	BANK2	$570.00	SAVINGS	Asset
3/28/20 1:20 AM	MORTGAGE	BANK2	$575.00	MORTGAGE	Liability
3/28/20 1:20 AM	VISA	BANK2	$580.00	OTHER	Liability
3/28/20 1:20 AM	CHECKING	BANK2	$585.00	CHECKING	Asset
3/28/20 1:20 AM	SAVING2	BANK2	$590.00	SAVINGS	Asset
3/27/20 8:16 PM	VISA	BANK2	$595.00	OTHER	Liability
3/26/20 8:54 PM	VISA	BANK2	$600.00	OTHER	Liability
3/26/20 8:54 PM	SAVING2	BANK2	$605.00	SAVINGS	Asset
3/26/20 8:54 PM	CHECKING	BANK2	$610.00	CHECKING	Asset
3/25/20 6:05 PM	VISA	BANK2	$615.00	OTHER	Liability
3/25/20 6:05 PM	CHECKING	BANK2	$620.00	CHECKING	Asset
3/25/20 6:05 PM	SAVING2	BANK2	$625.00	SAVINGS	Asset
3/24/20 5:05 PM	VISA	BANK2	$630.00	OTHER	Liability
3/24/20 5:05 PM	CHECKING	BANK2	$635.00	CHECKING	Asset
3/24/20 5:05 PM	SAVING2	BANK2	$640.00	SAVINGS	Asset
3/23/20 8:13 PM	CHECKING	BANK2	$645.00	CHECKING	Asset
3/23/20 10:53 AM	VISA	BANK2	$650.00	OTHER	Liability
3/23/20 10:52 AM	CHECKING	BANK2	$655.00	CHECKING	Asset
3/23/20 10:52 AM	SAVING2	BANK2	$660.00	SAVINGS	Asset
3/21/20 10:32 PM	MORTGAGE	BANK2	$665.00	MORTGAGE	Liability
3/21/20 10:32 PM	VISA	BANK2	$670.00	OTHER	Liability
3/21/20 10:32 PM	SAVING2	BANK2	$675.00	SAVINGS	Asset
3/21/20 10:32 PM	CHECKING	BANK2	$680.00	CHECKING	Asset
3/20/20 6:02 PM	VISA	BANK2	$685.00	OTHER	Liability
3/20/20 6:02 PM	SAVING2	BANK2	$690.00	SAVINGS	Asset
3/20/20 6:02 PM	CHECKING	BANK2	$695.00	CHECKING	Asset
3/20/20 6:02 PM	CHECKING	BANK2	$700.00	CHECKING	Asset
3/19/20 6:06 PM	CHECKING	BANK2	$705.00	CHECKING	Asset

[v-deddai1-msft]

Hi @BLAP33 ,

We can use the following measure in visual filter to filter out the latest balance for each account(If it is the latest in the current month, it returns 1, otherwise it returns -1)

Measure =

VAR m =

    CALCULATE (

        MAX ( 'Table'[Date] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[Account] IN DISTINCT ( 'Table'[Account] )

                && MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )

                && YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )

        )

    )

RETURN

    IF ( m IN DISTINCT ( 'Table'[Date] ), 1, -1 )

Then we create a table visual (drag the 'Date','Account' and 'Class'column in it ) and add the measure in the visual level filter:

Lastly, we can add a month slicer to filter the latest balance for each account in each month:

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW48Hby5COVBk...

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

Best Regards,

Dedmon Dai

[BLAP33]

@v-deddai1-msft ,

Thank you for taking a look.  I copied the DAX straight from your post, but getting an error on the syntax for 'return' is incorrect.  I am not exactly sure how to access your pbix file as the link was removed.  This looks close to what I am trying to do; however, would this populate a 1 for the last entry for January, February, AND March?  It looks like it would only include the latest month, where I am interested in trying to populate a chart on a monthly basis.  

Thank you again!  Appreciate the support as always!

[BLAP33 · Accepted solution]

As a follow up, I did figure it out.  It's clunky and I know there is easier syntax; however, my dataset is not huge so it works perfectly.  For reference, here were the steps I took (I am sure some of these can be combined, but I am relativily new to PowerBI):

#1) Converted the date column to a decimal via FORMAT (Date Column, "general number")

#2) Added a month integer column so I can see MONTH END type data per acccount via MONTH(Date Column) - Later this year, I will have to add year to this so as data rolls into the next year its not repeating and replacing.

#3) SUMMARIZED the table via  SUMMARIZE(TABLE, TABLE[ACCOUNT], TABLE[MONTH INTEGER], TABLE[CLASS], "Month End Decimal Date", MAX(Table[Decimal Date])) - this took the original table and provided a single line item per MONTH INTEGER per Account per Class, and the key was it found this on the MAX DECIMAL DATE, which would be the latest time stamp for that month integer.  

#4) I used the Month End Decimal Date summarized in step 3 to look up the value of the balance on the previous table (i had to setup a relationship to do this).  I simply did LOOKUPVALUE(Table[End of Month Balance], Table[Account], SUMMARIZEDTABLE[Account], Table[Month Integer], SUMMARIZEDTABLE[Month Integer], Table[Decimal Date], SUMMARIZEDTABLE[Month End Decimal Date]) - this searched my original table for the row containing exactly the same account, month integer, class, month end decimal date (calculated in step #3), and pulled the balance on that time stamp.  

This gave my a column with my month end balance for each period, each account, each class to chart.  Thank you to looking into this and providing some insight as PowerBI gives you so many ways to solve your data problem!  Love this software!  

Thanks again!

[CheenuSing]

Hi @BLAP33 ,

Given the sample data what is the output expected ??

Cheers

CheenuSing

[BLAP33]

@CheenuSing 

Thanks for taking a look!  Sorry for the description not being totally there!  There are a couple options, but my end goal would be to have a single column with the month end value/balance of each account.  For example, given the sample data:

As March progressed, I would like to have a summary table of the distinct accounts (Visa/Savings2/Checking/Mortgage/etc) with the latest balance populated.  So for TODAY, it would show or populate a table with month end for these accounts as:

3/31/2020  - VISA - $545 - Liability

3/31/2020 - SAVINGS2 - $550 - Asset

3/31/2020 - CHECKING - $555 - Asset

3/28/2020 - Mortgage - $575 - Liability (since no other recent information is provided)

the rest of the march dates would be ignored as they are older data for these accounts.  April would just show the date closest to TODAY if possible.  Is this possible?