Last Sale Amount- LASTNONBLANK()

Power BI Data Model
1

Hi,

I’m trying to calculate the last Sale Amount in a Sales Table. (See screenshot of my model below) My measure is as follows;

Last Sale Amount = CALCULATE( [Total Sales] , LASTNONBLANK( Dates[Date] , [Total Sales] ) )

I get $0. As well for the Last Sale Date I get the last date in my Date Table. So it seems my relationships are not working correctly.

The moment I make the Date and Invoice tables bi-directional it works. But then breaks alot of my other measures with below screenshot errors.

Any help would be great.

Thank you

Cameron

50 pm
Screen Shot 2018-01-18 at 2.41.50 pm.png1426×716 74.2 KB

2

large

3

Hi Cameron,

Is there by chance a date within the sales table you could use here.

Last Sales Amount = 
VAR LastSalesDate = MAX( Sales[Purchase Date] )

RETURN
CALCULATE( [Total Sales] ,
	FILTER( ALL( Dates ),
		Dates[Date] = LastSalesDate ))

Something like the above.

I personally try to avoid multi directional relationships where ever possible. Do you think they are absolutely necessary here? You’ll find you’ll be compensating everywhere in your measures if you leave it like this.

Not sure if you’ve gone through the Advanced Data Modeling course at all but I go through a lot there

Can you attempt this one and let me know how it goes.

Getting my own head around all those multi directional relationships I find quite difficult also.

4

Thank you Sam. It is working on most situations and I have removed the bi-direction. It is not filtering multiple customers. See below screenshots. It works for All Customers and selection of 1 Customer, but not filtering multiple customers

Last Sales Amount 2 = 
VAR LastSalesDate = MAX( Invoices[Date] )

RETURN
CALCULATE( [Total Sales] ,
FILTER( ALL( Dates ),
Dates[Date] = LastSalesDate ))

All Customers
All Customers.png1934×874 168 KB

5

1 Customer
1 Customer.png1912×860 163 KB

6

Multiple Customers
Multiple Customers.png1944×1066 344 KB

7

That seems odd. It should be working. Based on what the formula and model looks like, unless I’m missing something.

Which client actually had the last sale and was there any amount attached to it from this example?

The formula will be looking at a filtered table for all those customers. Then seeing which was the last day a sale occurred across all of them, and then summing up the total sales only for that last day.

Can we break it down a bit, and look to audit which customer had the last sale out of that selection and then work out what day it was on.

8

Just another big recommendation here…auditing things like this are simplified immensely when the model is set out in a intuitive way as I go through in the advanced modeling course.

If you have some spare minutes to set it up like this is will help a lot with this one. Chrs

9

Where does Customer List dimension come from also? I’m presuming Customer table

10

Hi Sam, I have complete the Advanced Data Modelling Course.

The data source I’m sharing is the Demo Company in Xero Accounting. The difference I have with your course is that the sales table does not have a date column. But it does have a pointer to the Journals table. Then the Journals tables has a pointer to the Invoices table wit the sales date I need, plus the relationship to the Customers.

In summary it would be terrific if I could bang together the Invoices, Journals and Sales table together. I tried this using merge in the Query Editor but I couldn’t load the model. As the tables with proper clients are all holding over 500k in rows each. It could not handle the load of matching them all up… I just received errors. So instead reverted back to to having my data model flow through with relationships.

But I wonder, if this is the problem because Customers is coming through Invoices and Sales is coming through Journals…

Hmmmm

11

Hi Sam, in testing I think your formula is in fact working. When multi selecting customers I’m getting some unusual behaviour where it works on some customers and not on others. The Measure is great and I believe unrelated.

Would still love to hear you opinion on the model though. To have the Invoices - Journals - Sales table line. Is there any reason why this is a problem? I should still be able to slice and dice by Date, Customer, Salesperson etc…

Cheers

Cameron

12

That’s great. I was wondering if you are able to add the file here. I can have a look at the model and see how I would structure it.

I was also going to audit the formula and see why the results weren’t coming out as expected.

13

Hi Sam, sorry for the late reply. It will not let me upload the PBIX file. It is not a valid format. I have just emailed it to you. Cheers, Cameron.

14

Got the model thanks.

Here are my thoughts. (I can’t do anything in the query editor unfortunately due to credentials)

If possible I would attempt to merge Journals with Sales/Expenses/Direct Costs/Other Income. I’m see that the table are only 300 row long. Is that right?

I find merges work well even on relatively large data sets

image
image.png1465×771 48.9 KB

What I would first do though is append Sales/Expenses/Direct Costs/Other Income before you merge. They all have the same columns from what I can see.

This would really simplify things immensely if you can do this and make all DAX formula much simpler.

Let me know if you can do this.

15

I’ve checked the formula also and I think it works. There just some clients who have 0.00 as the last sale amount. If one of those client is in the selection and they have the last sale date then that’s why it returns 0.00 I believe

This is how I check below creating a table of last sale amount by customer. Then I ran a few scenarios. It seemed to work as expected based on the data in the table.

image
image.png1166×614 63.4 KB

16

Ok, thank you Sam. I will give merging in the query editor another go. Merging previously was not a good outcome. As this is just the Xero Demo Company with only 300 rows. The rows with clients were carrying over 500k in rows for each table. It was not coping and crashed/timed out during long waits.

Which is why I was hoping I could simply pass the relationships through the tables in the model, but if you think my DAX measures will be compromised/limited with this structure, I will continue testing/refining the model.

Thank you again.

Cameron

17

Ok great. Try the append of those tables first as then you will only have to complete one merge versus 4.

If it does continue to create issues I think the way I have set up the model would still be ok, it would just take more work on the measure side.

18

This is normal behavior of a subsidiary ledger. The Sales Table will represent the Accounts Receivables Subsidiary ledger holding invidual invoice items. The Journals refer to the general ledger only and not customers which holds the value only and is what the trial balance is based on.

I have found it useful to pull that FACT data for each ledger in group . ie General Ledger/Journal Transactions, Sales Invoices, Purchase Invoices etc.

Perhaps post an image of your data model. Have you created your own separate Date table?

Also according to Xero docs https://developer.xero.com/documentation/api/invoices

There are two dates on sales:
Date invoice was issued - YYYY-MM-DD
DueDate Date invoice is due - YYYY-MM-DD

Perhaps you are not grabbing the API data correctly?

19

Hi Gary.

I’m really sorry I missed this response from you. I did not receive an email notification telling me that you had contributed. Only a notification on the Enterprise Forum when I logged back in, which is the reason for the delay in more response.

I have created my own date table… (It is the Date Table Sam has shared, with the addition of financial year columns by month, year & quarter).

I am now getting the correct behaviour for Last Sale Amount after using Sam’s new DAX Expression.

Thank you for your thoughts.

Cheers

Cameron

20

Hi, no probs glad Sam sorted out your issue!