Forum Discussion

HEW's avatar
HEW
Helper III
4 years ago

Calculate deviation% based on first available value

Hi.

 

I am trying to calculate the deviation% from the first available value compared to the last available value in selected period.

 

As you can tell from below matrix, the first row in the matrix is correct. However, the second row is wrong as there is no value in January 2021. The deviation % should be 23,6% (increase from 8,74 to 10,8).

 

The dataset looks like below for the second row (article):

 

And the value is based on below 2 measures:

 

Diff =
VAR c_article = SELECTEDVALUE ( 'Historic_Gross_price'[Materiale] )
VAR _min = minx (allselected(DimDate), DimDate[FullDate] )
VAR _max = maxx (allselected(DimDate), DimDate[FullDate] )
RETURN
CALCULATE (
MAX ( 'Historic_Gross_price'[Beløb] ),
'Historic_Gross_price'[Materiale] = c_article,
_max >= 'Historic_Gross_price'[Gyldig fra],
_max <= 'Historic_Gross_price'[Gyldig til]
) -
CALCULATE (
MAX ( 'Historic_Gross_price'[Beløb] ),
'Historic_Gross_price'[Materiale] = c_article,
_min >= 'Historic_Gross_price'[Gyldig fra],
_min <= 'Historic_Gross_price'[Gyldig til]
)
 
And:
 
Dev test =
VAR c_article = SELECTEDVALUE ( 'Historic_Gross_price'[Materiale] )
VAR _min = minx (allselected(DimDate), DimDate[FullDate] )
VAR _max = maxx (allselected(DimDate), DimDate[FullDate] )
RETURN
DIVIDE([Diff],
CALCULATE (
MAX ( 'Historic_Gross_price'[Beløb] ),
'Historic_Gross_price'[Materiale] = c_article,
_min >= 'Historic_Gross_price'[Gyldig fra],
_min <= 'Historic_Gross_price'[Gyldig til]
) )
 
Any suggestion on how to change the measures, so it is not based on the first date, but the first value within the data range? I am really stuck!
 
Thanks a lot in advance.
Helen

8 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    Try using FIRSTNONBLANKVALUE & LASTNONBLANKVALUE for the data you are comparing e.g.

     First Value = FIRSTNONBLANKVALUE(DimDate[FullDate, MIN('Historic_Gross_price'[Gyldig fra]))

                                                                                                MAX (above I'm not sure of your table-field name after MIN

                                                                                                AVG

    all should work and your time slicer will get these starting - ending values and then you can do your comparisons from here. I hope that helps.

    • Whitewater100's avatar
      Whitewater100
      Solution Sage
      4 years ago

      I missed a bracket

      First Value = FIRSTNONBLANKVALUE(DimDate[FullDate], MIN('Historic_Gross_price'[Gyldig fra]))

    • HEW's avatar
      HEW
      Helper III
      4 years ago

      Whitewater100  thank you for taking the time to reply.

       

      I would like the date slicer to be flexible and it should not adjust according to the starting dates. If, for example, I select year 2020, I would like the cost prices for the first date of every month shown in the matrix - and the deviation % should be calculated based on the prices. If the first price is in May 2020 (article not purchased earlier) this should be the first value.

       

      Do you follow me? It is a bit difficult to explain 🙂

    • HEW's avatar
      HEW
      Helper III
      4 years ago

      Whitewater100  Thank you for the solution, which works perfectly in your case - and is just what I need. 

       

      My dataset is, unfortunately, not build up with a row per day, but with a date range. F. ex. a purchase price is valid from Oct. 1 2018-Dec. 31 2019. And theres a new prices valid from Jan 1. 2020-current date.

      I have tried to convert the date range to a list of dates to have the same layout as your solution, but it wouldn't work due to the amount of data.

       

      Any suggestions how to solve the adjust your solution considering my date ranges?

      Thanks a lot in advance.

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi Helen:

     

    I'm wondering if you are able to share any data (data model layout) and sample data? I beleive you might need a disconnected table with item, price, price start date, price end date.

     

    Thanks!

    • HEW's avatar
      HEW
      Helper III
      4 years ago

      Whitewater100 

       

      I have tried to upload a test model 😊 Hope it is working....

       

      https://drive.google.com/drive/folders/1yNBHFdd9I40Tv7CFXCjV0y62GbRYlTda?usp=sharing

       

      For article "Test 1" there is no purchasing value in the first month, March 2020, so I would like the start value for this article to be September 2021 as this is the first time we purchase this article. This will give us on increase on 2,06% (from 5.435 to  5.547).

       

      I really appreciate that you are taking the time to help - thanks a lot.

      Helen

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hi Hew:

        OK. The first suggestion is to create a relationship between DATE[Date] with the MaterialTable {Valid From] Date.

        Then you can have several measures.

        Avg Price = AVERAGE(Historic_Gross_Price[Price])
        FirstValue = FIRSTNONBLANKVALUE('Date'[Date],[Avg Price])
        LastValue = LASTNONBLANKVALUE('Date'[Date],[Avg Price])
        New Diff = [LastValue] - [FirstValue]
        New Dev % =
        IF(HASONEVALUE(Historic_Gross_Price[Material]),
        DIVIDE([New Diff],[FirstValue],0))    note change format to percent 2 places. I hope this helps!