Previous Last Non Blank Month's Value

[SMF_DATA]

Hello everyone,

I'm new in PBI and DAX, I need to calculate avearage unit price changes. I have two tables: Purchase and Date 

I calculated the avearage unit prices, shown in screenshot 1, and I need an output shown in screenshot 2. Appreciate any help, thank you.

SS1-Data

SS2-Desired Output

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[SMF_DATA]

@Ashish_Mathur 

Thank you for the reply. You can download the file : https://www.dropbox.com/s/5esil5nzycteiah/file.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[SMF_DATA]

Hi again @Ashish_Mathur . Sorry for the late response, I was dealing with some health issues. Your solution seems working but if there are multiple purchases in the same month, only calculates the latest avg unit price in that month. I need the whole month's avg unit price. Thank you for your help.

[Ashish_Mathur]

Hi,

See if this revised measure works

Measure = if(ISBLANK([AvgUnitPrice]),CALCULATE([AvgUnitPrice],DATESBETWEEN(DateMain[Date],[Last date for which AvgUnitPrice is available],[Last date for which AvgUnitPrice is available])),[AvgUnitPrice])

[SMF_DATA]

Hi @Ashish_Mathur ,

Still brings the latest date's average unit price for the no purchase months. I need 0.2010 as average unit price for may 2019 for the filtered item id.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[SMF_DATA]

Now it is working like a charm 🙂 . Thank you so much @Ashish_Marthur, appreciate your help. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi Ashish,

I need your help for one of my project regarding Power BI,

 I have a huge database of Inventory Management having More than one stores as well as products, 

once a week or month stock is updating by some value(UpdateQty) and having one min value(MinQty)

I calculate values like - Out-of-Stock, Under-Stock, In-Stock using calculation (depend on UpdateQty and MinQty),

Finally, I create a calculated column as StockCheck, which shows the status of every product.

Now I rt(Rows- (StoreNo & Product), Column-(Dates), Values-(StockCheck)),

but it shows blank values if stock is not updated on that date (If the product does not update means take last updated value)

 and that dates are not in the database.

So my problem is that I have to calculate the last value on that blank values, I have attached a sample file 

Please help me out.

Thanks,

[Ashish_Mathur]

Hi,

There is no sample file.  Share a simple Excel file and show the expected result there.

[Anonymous]

Plz find the pbix file below

https://sparinformation-my.sharepoint.com/:u:/g/personal/atul_patil_sparinfosys_com/ETsO5D4iuA1Bj_vT...

[Ashish_Mathur]

Hi,

I still do not understand what you want.  Show your data and expected output in a simple Excel Table.

[Anonymous]

Hi Ashish,

Please find below the snapshot of the current status,

As take an example,

1.for the product "43 OZ ChocolatePudding Cake", status is Out-of-Stock on 1st of Dec so until next status it shows Out-of-Stock

2.for the product "43 OZ Vanilla Pudding Cake", status is Under-Stock on 5th of Dec, so until next status it shows Under-Stock, and so on

I hope, you understand the problem,

Thank You so much

[Ashish_Mathur]

Hi,

I can try to help.  Share the source Excel file which you uploaded into PowerBI Desktop.

[Anonymous]

Hi Ashish,

Thank for helping and giving your important time.

Please find here the link for Excel file, which contains two sheets 

1. In the first Sheet, it contains Database

2. In the Second sheet, contain the other information to add another column

https://sparinformation-my.sharepoint.com/:x:/g/personal/atul_patil_sparinfosys_com/EarF7iiHxL5EgicT...

Thank You

[Ashish_Mathur]

Hi,

I could not connect your PBI file to your source Excel file.  Probably because there are some missing/different/additional columns in the Excel file.  Keep the question simple - based on the MS Excel file that you have shared with me, show me your expected result.