Divide one value by reference value in a matrix (trend calculation)

[ab_99]

Hi dear Power BI Community,

I have this following issue: 

I have a matrix with years in rows and months in columns.

Now I want to perform a trend calculation as follows:

Value from 2023-1 (1.834) is for all following periods the reference value (the value from the earliest month and year basically). I need to devide each value from the other months by the reference value. 

E.g.:

2024-02:     2.618/1.834

2023-04:     12.498/1.834

and so on. 

How can I achieve this with a measure?

Thanks in advance!

[Selva-Salimi]

Hi @ab_99 

Is it a fixed value for all month and all years?! if so, then you can write a measure as follows:

measure rate :=

var ref = calculate (max(X) , filter (all(table) , year(date)= 2023 && month (date) =1))

return

divide ( selectedvalue( X) , ref)

 ** X is the column that contain tehse values **

If this post helps, then I would appreciate a thumbs up 👍  and mark it as the solution to help the other members find it more quickly. 

[ab_99]

Hi @Selva-Salimi 

thank you for your quick reply.

The month and year isn't fixed. I have a between-slicer where I select the year-frame I'd like to see. 
The value has to be variable and must come from the first month of the earliest year selected.

[Selva-Salimi]

ok, then you can rewrite as follows:

var ref = CALCULATE(max(X), filter(ALLSELECTED(table), [date] = min (date))

return

divide ( selectedvalue( X) , ref)

 ** X is the column that contain tehse values **

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[ab_99]

Unfortunately, it is not working. I put only the ref variable in a measure and this is the result: 

The result should be everywhere 1.834, if this dax expression works.

[Selva-Salimi · Accepted solution]

would you try this solution:

var min_date= calculate(min(date), allselected(table))

var ref = CALCULATE(max(X), filter(ALLSELECTED(table), [date] = min_date)

return

divide ( selectedvalue( X) , ref)

 ** X is the column that contain tehse values **

if still not working, please provide some details about your tables, model and relationships.

[ab_99]

Thank you very much! That did the job. 🙂