DAX - Get Last Day of Month Balance For Every ID and Month

[PBI_Member_01]

Greetings Power BI Community,

I have a dataset connected to DirectQuery Storage and the sample dataset I have is as below:

So to Describe the above screenshot, the table on left is what my expected outcome would look like.
The colors represent different tables from where columns are being pulled from.
The right side shows how Table A,B,C,D are connected in the Data Model. Table A is connected with Table B on the basis of Cust Acc ID. Table C is connected with Table A on the basis of Cust Acc ID. Finally, Table D, which is basically a Calendar table, is connected with Table B on basis of Trans DT (Not the Last Trans DT shown in the expected outcome)

The Table C has a Balance Date column and Cust Balance column for every date. This might have null values for some given dates based on the source data as shown in below screenshot:

So I want to show the End of Month Cust Balance value, from Table C, for every Cust Ref ID against the Month-Year in my expected outcome. I have tried using SUMX and SUMMARIZE functions to somehow link this data coming from different tables but the numbers I am getting are way off.
Also, if the Cust Balance is NULL for any month end date, we should still show that since that is how it is coming from the source.

I am still in my early stages of learning and implementing DAX so any help on this would be highly appreciated.

Kind Regards

[Anonymous · Accepted solution]

Hi @PBI_Member_01,

According to your description, you want to get last day of month balance for every id and month.

Here are my steps you can follow:

(1)This is my test data.

Table A:

Table B:

Table C:

Table D

(2)This is the relationship between the tables.

(3)We can create a measure: “Cust Balance” or “Cust Balance2”

Cust Balance =

var _a = CALCULATE(MAX('Table C'[Balance Date]),FILTER('Table C',MONTH([Balance Date])=MONTH(MAX('Table B'[Last Trans DT]))))

return CALCULATE(MAX('Table C'[Cust Balance]),FILTER('Table C',[Balance Date]=_a))

Cust Balance2 = var _cu_last=MAX('Table B'[Last Trans DT])

var _cu_end= EOMONTH( _cu_last ,0)

var _last =MAXX( FILTER( 'Table C' , 'Table C'[Balance Date] =_cu_end) , [Cust Balance])

return

  _last

(4)Then we can meet your need , the result is as follows.

If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.

Best Regards,

Neeko Tang

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

[PBI_Member_01]

By tweaking this measure a little bit, I was able to reach my expected outcome.

You have been of great help Neeko. Thank you for your detailed explanation and breaking down the solution. Took me few hours but eventually I got there.

Big Thanks once again and Kind Regards.

[PBI_Member_01]

Hi @Anonymous 

Thank you for your effort and response on this issue.

So I have noticed that you are using Last Trans DT as Date reference in Table D. That is not how the Calendar table has been made. Allow me to demonstrate with below attached images:

There is another column Cust Acc ID on the basis of which these tables are connected in Data Model. Just for reference, see below image.

The first column on the left is what I am talking about. This, although, is not required in the expected output of report, which is just a Table Matrix.
Now, just to share what I have done so far, I managed to create a measure as was mentioned by amit using:

calculate(lastnonblankvalues(date[Date], sum(Table[Cust balance])), datesmtd(date[Date]))

This measure was created in Table C, and if I drag Month with Year from Table E (which is not being used in the output) , it does give me the last non blank balance for the Cust Ref ID, for every month available.

Now if my Table C looks like this:

What happens is when I drag this Measure that I have created, to my report structure with all the other columns, I get the final output something like this.

Which basically means, it takes the last available amount, for a specific ID and tags it against every month available. (Bear in mind the Month With Year Column needed on the report is from Table D)
Whereas If I just use my measure against columns from Table C and use Month With Year from Table E (which is not required on the report) it shows correct Last available Month Value for Every month and tags it correctly against IDs.

I have tried my best to explain what is going on so far and unfortunately, I am very short on time. But I hope you do understand what I am dealing with here.

Is there a way to show the output in a better way so that it corresponds to every value correctly?

Your input on this is highly regarded.

Big thanks in advance.

[PBI_Member_01]

So just an update, I have managed to create the measure which gives me the End of Month balance, IF available on Last given day of every month. But as shown in the first picture of my original post, if I drag the Cust Balance along with other columns, it displays empty for every row.
But if I create a seperate table for test purposes, taking columns only from this Table C, just to see values coming in this measure, it does show the values.

Can someone please help me out with this?

Big thanks in advance

Regards

[PBI_Member_01]

Any suggestion on this will be appreciated.

[PBI_Member_01]

Is there no way I can use RELATEDTABLE to calculate this measure for the current context?
For all I know so far, Either I have to pull all the common information from Table C (except for the Table B Last Trans DT, Last Trans Amount which are DAX Measures created around Table B Context)
Or keep the structure same and somehow cater for this new seperate measure.

[amitchandak]

@PBI_Member_01 ,  You need measures like , prefer seperate date and customer table joined with your table

calculate(lastnonblankvalues(date[Date], sum(Table[Cust balance])))

or

calculate(lastnonblankvalues(date[Date], sum(Table[Cust balance])), datesmtd(date[Date]))

or

calculate(lastnonblankvalues(date[Date], sum(Table[Cust balance])), datesmtd(date[Date]), filter(allselected(customer), cuctomer[Customer] =max(Customer[customer]) ))

[PBI_Member_01]

Hi @amitchandak 

Thank you for your help regarding this.
Just an update, I did use one of the possible solutions proposed by you and it does give me the required Balance Amount when I use it seperately only for columns of Table D

calculate(lastnonblankvalues(date[Date], sum(Table[Cust balance])), datesmtd(date[Date]))

However when I drag it along side the other columns for my report, which are coming from Table A, Table B, Table C (as shown in the picture of my original post), it only gives me one static latest last month value for each customer instead of showing it for every respective month.

Can you please guide me on how I can show it on my report for every month? I have shown in the picture how the tables are related in my Data Model.

Any help on this will be highly appreciated.

Thanks in advance