Calculate % deviation of beginning and end value in Matrix

[HEW]

Hi.

I have a matrix, where I show the delelopment in cost prices and I would like to have a column at the end showing the % deviation from the beginning value to the end value. For example, the value for the first row should be 10,59%. I would like it to be dynamic according to the date range selected in the slicer.

Is this possible? 

The cost price value is based on below measure:

PricePerStartOfMonth =

VAR c_article = SELECTEDVALUE ( 'Historic_Gross_price'[Materiale] )

VAR c_date = SELECTEDVALUE ( DimDate[FullDate] )

RETURN

CALCULATE (

MAX ( 'Historic_Gross_price'[Price] ),

'Historic_Gross_price'[Materiale] = c_article,

c_date >= 'Historic_Gross_price'[Date start],

c_date <= 'Historic_Gross_price'[Date end]

)

My table has below structure:

Historic_Gross_Price table
Article no.	Date start	Date end	Price
1	01.01.2018	30.11.2021	12,00
1	01.12.2021	31.12.9999	15,00
2	01.01.2018	31.12.9999	22,00
3	01.01.2018	31.01.2022	25,00
3	01.02.2022	31.12.9999	26,00

Thanks a lot in advance.

Helen

[amitchandak]

@HEW , we can change the grand total usng the final measure to show diff in grand total

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'[Price] ),
'Historic_Gross_price'[Materiale] = c_article,
_max >= 'Historic_Gross_price'[Date start],
_max <= 'Historic_Gross_price'[Date end]
) -
CALCULATE (
MAX ( 'Historic_Gross_price'[Price] ),
'Historic_Gross_price'[Materiale] = c_article,
_min >= 'Historic_Gross_price'[Date start],
_min <= 'Historic_Gross_price'[Date end]
)

final meausre =
If(isinscope(DimDate[FullDate]), [PricePerStartOfMonth], [diff])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[HEW]

@amitchandak 

Thanks a lot for taking the time to help. It is really appreciated!

I managed to solve the issue with the final measure and it seems to work with below:

Dev% =

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'[Price] ),

'Historic_Gross_price'[Materiale] = c_article,

_min >= 'Historic_Gross_price'[Date start],

_min <= 'Historic_Gross_price'[Date end]

) )

However, I do have a problem if theres no value in the first column. Any suggestion how to adjust the measure to use the first available value in selected period?

Thanks a lot!

Helen

[HEW]

Thanks a lot for your reply.

I must be doing something wrong as I get a wrong result:

It seems like theres no begin value...