I need help with a calculation of a last date in power pivot excel. I have a dataset with stock count of all masterdata in our company from xxxx to the end of actual month. Then I have a dataset where I take the last date of consumption or sale for masterdata from the first table via RELATED function. Then I have a basic formula "ROUNDUP((stock count date - last move date)/30;0)" for date difference in months and based on this information I am able to calculate deduction and impairment, but. When I create a flat pivot table and filter other period (stock count date) than the actual one I receive a wrong date because dates in the past show the last move of the last uploaded month. Would you advise how can I get a correct result.
For example:
| Material | Stock count date | Last move date | Stock value | Months w/o move | Deduction % | Impairment | Impairment € |
| 50700000000654 | 29.02.2024 | 18.03.2024 | 3248,99 | -1 | 100% | 3 248,99 | 128,39 |
Could you please upload a file with some example data, so that we can see the structure of your model a the measures you have create already.
Hi Riny, thanks for reply.
There is the excel file in the attachement. It is a sample with random values but the logic is same as in my original huge file.
Problem I would like to solve is formula for column "Last move date"/"Last move date edit" where I would like to have the date for the last move of each product but maximum date should not be higher than "Stock count period".
Thanks! I'm on a Mac right now and can't work on files with Power Pivots in them. Will be off to something else soon so will get back to you tomorrow (from a PC), if not someone else hops in in the meantime.
Have looked at your file but it's a bit difficult to jump in without knowing what exactly you have done. You connect on data on your own computer, load it into the DM and then add calculated columns to the StockCount table.
My gut feeling is that you should be able to do most of that in PQ and with some DAX measures in the DM. And I think you need a Calendar table and determine the latest move date for each product per period.
But as said, I'm not sure as I fully understand your model.
That's exactly how you have described. Few excel files loaded to PQ editor, cleaning data and then connection to DM, making relationships and then added calculated columns to the Stock count data set. I am just beginner so I am not aware of all possibilities for all functions. I tried to find combinations like MAX, LASTDATE, FILTER but I don't know how to achieve the result.
Couldn't get it to work properly with Power Pivot as I'm just not smart enough, haha!
Therefor a solution in Power Query (PQ) only for what I believe you are trying to calculate. And that is the net stock value after deducting an amount for obsolescence, depending on the type of product and the months since the last move in relation with the selected period. Hence, the last move date can never be later than the selected date.
I believe it's quite straight-forward in PQ. would that work for you?
