Calculation based on last known value in time

[marieke]

Hi all,

at the moment I’m trying to get our stocklevel values and we would like to compare these over the last months. However, at the beginning of this year, only stock level was registred in our database, not the product price at that moment.
So, I would like to calculate the stock level value for the first months against the first known value. So in this case, the value on 1-4-2020 needs to be used to calculate stock level values for Jan, Feb and March.

and then it should not change when I change date filters on my page, so when I’m looking at only Nov and Dec, it should not change to € 6,- per product.

Does somebody know how to fix this?

Kind regards, Marieke

[BrianJ]

@marieke,

This code is embarassingly verbose, but it works. I’ll work on slimming it down/optimizing it, but here it is for now:

Adj Price = 

VAR SelPrice = SELECTEDVALUE( Data[Price] )

VAR vTable =
FILTER( ALL(Data), Data[Price] <> BLANK() )

VAR FirstPriceDate =
CALCULATE(
    MINX( vTable, [Date] ),
    REMOVEFILTERS( Data[Date] )
)

VAR FirstPrice =
CALCULATE(
    MINX( vTable, [Price] ),
    FILTER( vTable, [Date] = FirstPriceDate )
)

VAR Result =
    IF( SelPrice = BLANK(), FirstPrice, SelPrice )

RETURN
Result 

Hope this is helpful. Full solution file provided below.

• Brian
  eDNA Forum - Find First Price Solution.pbix (375.5 KB)

[BrianJ]

@marieke,

A bit less verbose:

Adj Price2 =

VAR SelPrice =
    SELECTEDVALUE ( Data[Price] )
VAR vTable =
    TOPN ( 1, FILTER ( ALL ( Data ), Data[Price] <> BLANK () ), Data[Date], ASC )
VAR Alt1 =
    CALCULATE ( MAXX ( vTable, Data[Price] ), REMOVEFILTERS ( Data[Date] ) )
VAR Result =
    IF ( SelPrice = BLANK (), Alt1, SelPrice )
RETURN
    Result

• Brian

[EnterpriseDNA]

Hi @marieke, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[marieke]

Hi @BrianJ

Many thanks for looking into this. I have just tried to translate your code into my original model, but it is not working properly. Most likely I’m doing something wrong, but I can’t figure out what.

I have now attached a dummy copy of the original report I’m working on, hopefull this will help in making my issue more clear.
for each day, I want to calculate the value of the stock. This is based on the Stockmovement History table. It should be QTY * Costs. But as you can see, the Costs column is not always filled in, as we only start doing this from 15-04-2020. So we want the period from 01-01-2020 te be calculated by the costs on 15-04-2020.

If the date filter is based on complete period from the database, the measure for “pricedate” and “costs on pricedate” seem to be okay. but if i change this date filter to only this month, it is changing the pricedate and costs to the first date in the selected period. But it should always be the values for 15-04 as that was the first day the cost field was filled in the database.

The measure 'Inkoopwaarde voorraad" was made by me, but this is based on the current product value and they may change over time, so this is not correct. The measure “Inkoopwaarde voorraad obv cost” should look at the cost field from Stockmovement history and this is working correctly from 15-04 onwards, but this needs to be filled for the period befor 15-04.

I hope you will be able to have another look at this and help me out with it

Thanks!

Voorraadbeheer Verkoop incl Kengetallen voorraad DUMMY.pbix

[BrianJ]

@marieke,

Sure - I’ll be glad to take a look at this and hopefully have a solution back to you later today.

• Brian

[BrianJ]

@marieke,

I’ve been working on this for a while, and I think I see the problem. In the example you first provided, there was only one price per date, however in the new post there are thousands of prices (actually, costs in the revised post ) per date.

Thus, two things will help me rewrite the measure to get you what you need:

1. the data set is too big to easily work with for testing purposes. In power query, can you please filter it to keep only one of every 750 records? This will still allow for a rigorous construct with 5+ costs per date (I would do this, but don’t have access to the base data)
2. given the multiple costs per date, what is the decision rule I should use for which cost to select associated with the first nonblank date?

Once I have this, I think it will be relatively straightforward to revise the work I did tonight and get you a working solution.

Thanks!

• Brian

[marieke]

Hi @BrianJ ,

I’m sorry I wasnt clear enough about having multiple products in the database, which is causing multiple prices per date.

In the database, for every day and for every product, the actual prize (costs) for that day and product is stored. This is because the products costs may vary over time.

Indeed this will give a large dataset. For each product, I want to have the first known costs. for most products this will be the costs on 15-04-20 as from that date, costs were added to the database. I think this will answer your question 2.

and for the first question, I have now only loaded the information for only 2 of the products, so the dataset is much smaller now.

Please let me know if this is okay for now, thanks a lot for all of your help!

Voorraadbeheer Verkoop incl Kengetallen voorraad DUMMY.pbix (517.4 KB)

[BrianJ]

@marieke,

This took some playing around with, but I think this measure gets you what you need. Thanks for paring down the dataset – that made it a lot easier to work with.

Adj Cost2 = 

VAR SelCost =
    SELECTEDVALUE ( 'Stockmovement history'[Cost] )
VAR vTable =
CALCULATETABLE(
    TOPN (
         1, 
        FILTER ( 
            ALLSELECTED( 'Stockmovement history' ), 
            'Stockmovement history'[Cost] <> BLANK () 
        ),
        'Stockmovement history'[Date],
        ASC 
    ),
    VALUES( 'Producten Uitgebreid'[product_id] )
)

VAR RetrieveVal =
    CALCULATE ( 
        MAXX ( vTable, 'Stockmovement history'[Cost] ), 
        REMOVEFILTERS ( 'Stockmovement history'[Date] ),
      FILTER(
          vTable,
          [product_id] = SELECTEDVALUE( 'Producten Uitgebreid'[product_id] )
      )
    )
    
VAR Result =
CALCULATE(
    IF ( SelCost = BLANK (), RetrieveVal, SelCost ),
    ALLEXCEPT( 'Producten Uitgebreid', 'Producten Uitgebreid'[product_id] )
)

RETURN
Result 

I hope this is helpful. Full solution file attached below.

• Brian
  eDNA Forum - Voorraadbeheer Verkoop incl Kengetallen voorraad DUMMY - Solution .pbix (524.0 KB)

[EnterpriseDNA]

Hi @marieke, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[marieke]

Hi @BrianJ,

many thanks, I will check it asap and let you know if it really works in my model, but as far as I can see now, it looks great!

Many thanks,

Marieke

[EnterpriseDNA]

Hi @Marieke, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!*

[EnterpriseDNA]

Hi @Marieke, we’ve noticed that no response has been received from you since the 24th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

[EnterpriseDNA]

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

[marieke]

Hi All and @BrianJ,

sorry for my late response to this topic. I finally had the time to look into this today and this is exactly what I need to get this sorted.

So many thanks for your help!

Kind regards,
Marieke

[marieke]

Hi @BrianJ ,

Not sure whether I should start a new topic, or reply to my previous issues, as I have another issue with my Stock Value calculation unfortunatelly.

I hope you will be able to help me once again with this.

I have used your calculation to calculate the total stock value (Adj Cost 2 * Stock Level). On each individually row this is working perfect, but the total value for all products, is giving an incorrect result.

As you can see on Pagina 1 in this report, total stock value for product 3793 is € 24.800 and for 4723 is is €74.795, which should be a total of € 99.595, but the calculation is returning a value of € 113.269.

I want to have the total stock values for all products at any given moment in time, mostly this will be at the beginning of a month.

Is there a way to fix this?

Kind regards, Marieke
eDNA Forum - DUMMY - Issues with Total.pbix (521.2 KB)

[BrianJ]

@marieke,

On a thread that has been previously marked “solved”, always best to start a new thread if you’ve got a significant new issue like this one - that way it will get maximum attention and the fastest response.

@Greg has put together an awesome compilation of resources on fixing iincorrect totals in the DAX Patterns section of the forum:

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

Have you been through this yet and tried the techniques he outlines?

• Brian

[marieke]

Hi Brian,

many thanks, I will have a good look at that topic and do my best to fix this all by myself.
In case I can’ft figure it out, I will open a new thread for it…

Thanks!