Create measure considering columns and rows of a matrix

[Anonymous]

Hi,

I created a measure to calculate the cumulative actuals based on the query new business basic. What is the suggested approach to make this measure adaptive to be used in a Matrix using different fields of the NewBusinessBasic query as columns and rows, e.g. products, vendors?

AnnualPremiumCum = CALCULATE(SUM('NewBusinessBasic'[AnnualPremium]); FILTER(ALLSELECTED('NewBusinessBasic');'NewBusinessBasic'[Policy_StartDate] <= MAX('NewBusinessBasic'[Policy_StartDate])))

[v-lionel-msft]

Hi @Anonymous ,
What error did you meet when you drag the fields into the matrix? Was it like this?

If yes , you can change the data type:

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Lionel, thanks for the help, no specific error but the number simply don't adjust to the columns and rows of the matrix. Which is not suprising, because the DAX statement needs adjustetment, psydo code:

AnnualPremiumCum = CALCULATE(SUM('NewBusinessBasic'[AnnualPremium]); FILTER(ALLSELECTED('NewBusinessBasic');'NewBusinessBasic'[Policy_StartDate] <= MAX('NewBusinessBasic'[Policy_StartDate]; CONSIDER Matrix Rows, CONSIDE Matrix columns)))

[v-lionel-msft]

Hi @Anonymous ,
Maybe you can try this DAX:

In general, when we calculate the cumulative we need to use the 'EARLIER()' function.

AnnualPremiumCum = 
CALCULATE(
  SUM('NewBusinessBasic'[AnnualPremium]),
  FILTER(
    ALLSELECTED('NewBusinessBasic'),
    'NewBusinessBasic'[Policy_StartDate] <= EARLIER('NewBusinessBasic'[Policy_StartDate])
  )
)

or

AnnualPremiumCum = 
CALCULATE(
  SUM('NewBusinessBasic'[AnnualPremium]),
  FILTER(
    'NewBusinessBasic',
    'NewBusinessBasic'[Policy_StartDate] <= EARLIER('NewBusinessBasic'[Policy_StartDate])
  )
)

(I wrote it by hand. There might be some grammar mistakes)

Regards,
Lionel