gross profit calculation

[jbirco]

hi:
i have a issue with calculating gross profit, my model has different tables for sales and purchases and both have dates, i have a measure to determine unit price which i multiply by sales qty and the i subtract this from sales amount to get a gross profit.
 my issue is that when i display a time period like months or quarters then if there were no purchases during this period, my profit margin would be %100.
how can i write that if there are no purchases during the displayed time period i should get the last purchase price prior to this time
thanks

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[jbirco]

in simple words i need the last ever purchase price regardles of the filter context on the matrix

[v-robertq-msft]

Hi, @jbirco 

Sorry, I can't get your expected result in detail through your description, would you like to post some sample data(without sensitive data) and your expected result, si that we can help you better.

What's more, you can try LASTNONBLANK() and LASTNONBLANKVALUE() first, and check if these functions can meet your requirement.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak · Accepted solution]

@jbirco , if the current month price null, you can test a measure like the one below

lastnonblankvalue(date[date], purachase[price])

O
calculate(lastnonblankvalue(date[date], purachase[price]), all(date))

prefer a common date table between two tables

To get the best out of the time intelligence function. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of your events. See :radacad sqlbi My Video Series Appreciate your congratulations.

[jbirco]

thanks a million for your time and effort, the purchase price had to be a measure, also i tried in the past lastnoblank, you gave me the gift of lastnoblankvaue.