Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

You need to log in to create posts and topics.

Dealing with cumulative data in Matrix – Semi-additive measure

Problem:

We have cumulative monthly data on for example investment and  want to show data in matrix with respect to categories and date (year and month). Should we use standard sum the calculation will not be correct. (values in January will be correct but values in February will be calculated as a sum of values in January and February etc…)

 

Sollution (measure):

Investment=

VAR LASTBALANCEDATE = CALCULATE ( MAX ( Investment_source_data[date_inv] ))

RETURN

    CALCULATE (

        SUM (Investment_source_data[value] );

        'datum'[DATE] = LASTBALANCEDATE )

 

Input:

Investment_source_data[date_inv] – column that contains date of investment

Investment_source_data[value] – column that contains actual cumulative investment

'datum'[DATE] – data table with date hierarchy used as input in Columns in Matrix (year,month)

 

Explanation:

Function Max() search for the last date for which there is data. Therefore LASTBALANCEDATE filters data to sum only Investment_source_data[value]  with the highest date in specific month.

 

 

For more information: https://www.sqlbi.com/articles/semi-additive-measures-in-dax/