cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

gross profit calculation

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User III
Super User III

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors