Forum Discussion
Calculation over a measure
Hi,
I need help to create a Measure that can divide between the values of a Measure called "Value_M"
I need to divide one value against the previous one. Example > Divide : Value_M (FY21Q2) / Value_M (FY21Q1)
1,278 / 1,249
BUT data doesn't have Date column. So all functions related to Dates like Dateadd, sameperiodlastyear, etc can't be use. The only columns to use are FY (Fiscal Year) that is a Text column like FY21, FY22 etc. And a the Quarter column that is also a text plus the number of the Quarter.
How can I create a measure using these 2 text columns as a criteria to divide these numbers ? I can extract the Fiscal year with the function RIGHT and same with the Q column, but I don't know how to use those Variables to create the measure.
Thanks !
Try it like this.
Add a measure to sum the value.
Value Amount = SUM ('Table'[Value_M] )Then use that in the QoQ % calc.
QoQ % = VAR _yq = SELECTEDVALUE ( 'Table'[Year QTR] ) VAR _q = INT ( RIGHT ( _yq, 1 ) ) VAR _y = INT ( MID ( _yq,3,2 ) ) VAR _newy = IF ( _q = 1, _y - 1, _y ) VAR _newq = IF ( _q = 1, 4, _q - 1 ) VAR _PriorQ = "FY" & _newy & " Q" & _newq RETURN DIVIDE ( [Value Amount], CALCULATE([Value Amount], 'Table'[Year QTR] = _PriorQ ) )
2 Replies
Try it like this.
Add a measure to sum the value.
Value Amount = SUM ('Table'[Value_M] )Then use that in the QoQ % calc.
QoQ % = VAR _yq = SELECTEDVALUE ( 'Table'[Year QTR] ) VAR _q = INT ( RIGHT ( _yq, 1 ) ) VAR _y = INT ( MID ( _yq,3,2 ) ) VAR _newy = IF ( _q = 1, _y - 1, _y ) VAR _newq = IF ( _q = 1, 4, _q - 1 ) VAR _PriorQ = "FY" & _newy & " Q" & _newq RETURN DIVIDE ( [Value Amount], CALCULATE([Value Amount], 'Table'[Year QTR] = _PriorQ ) )
