How to calculate a cumulative measure by id's month?

[iago_salvadori1]

Hello Power bi community,

I have the following data: MONTH_ID, PRODUCT, YEAR AND ID_CODE.

I would like to create a measure that would result in a column with the same behavior as the column "RESULT":

I tried to create a measure, but doesn't have the result expect. 

Result = CALCULATE([TOTAL],
         FILTER(
         ALL('TABLE1'[MONTH_ID]),
         'TABLE1'[MONTH_ID] < MAX('TABLE1'[MONTH_ID]))

The measure must be the value up to the previous month of the filter, that is, if no filter is defined MONTH_ID = 10, the cumulative result must be up to MONTH_ID = 9

[amitchandak]

@iago_salvadori1 , Not able to related total and result,

You need like

Result = CALCULATE([TOTAL],
FILTER(
ALL('TABLE1'[MONTH_ID]),
'TABLE1'[MONTH_ID] < MAX('TABLE1'[MONTH_ID]) - 1 )

Or have year month table/ date
new column , in a new month year table

Month Rank = RANKX(all('Date'),'Date'[MMonth Year],,ASC,Dense)

The try measure like 

Result = CALCULATE([TOTAL],
FILTER(
ALL('Date'),
'Date'[Month Rank] < MAX('TABLE1'[Month Rank]) - 1 )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[iago_salvadori1]

Hello @amitchandak, thanks your suggestion.

I don't know how to send Pbix here, but I'll send the table with the data:

ID_COMPANY	ID_CATEGORY	ID_MONTH	PRODUCT	YEAR	CLIENT	QTD
5600	10	1	Product A	2021	1105	40
5400	10	1	Product B	2021	1001	30
5600	10	2	Product C	2021	1001	30
5600	10	3	Product A	2021	1001	10
5400	10	3	Product A	2021	1105	40
5400	10	4	Product A	2021	1105	50
5400	10	5	Product A	2021	1001	10
5600	10	6	Product C	2021	1002	5
5600	10	7	Product C	2021	1105	5
5600	10	8	Product B	2021	1107	100
5400	10	10	Product D	2021	1105	30
5500	10	9	Product B	2021	1105	10
5500	10	11	Product Z	2021	1001	30
5400	10	11	Product A	2021	1105	25
5600	20	3	Product Z	2021	1001	20
5600	20	4	Product A	2021	1105	60
5400	10	12	Product B	2021	1107	30
5500	10	7	Product C	2021	1001	10

I create two measures:

Quantity MONTHLY (Category 10) = 
CALCULATE(
SUMX(Table1,Table[QTD]),Table1[ID_CATEGORY] = 10)

Quantity CUMULATIVE (Category 10) = 
CALCULATE([Quantity MONTHLY (Category 10)],
FILTER(
ALL(Table1[ID_MONTH]),
Table1[ID_MONTH] < MAX(Table1[ID_MONTH]) - 1))

With the following filters in the chart:

Graph result:

The measure CUMULATIVE Quantity  (Category 10) has the wrong result 330, It should be 340.

[v-rzhou-msft · Accepted solution]

Hi @iago_salvadori1 ,

Here I have a test by your sample data. I think you can use "less or equal to" function in your filter and you can achieve your goal.

Quantity CUMULATIVE (Category 10) =
CALCULATE (
    [Quantity MONTHLY (Category 10)],
    FILTER (
        ALL ( Table1[ID_MONTH] ),
        Table1[ID_MONTH]
            <= MAX ( Table1[ID_MONTH] ) - 1
    )
)

Result is as below.

Best Regards,
Rico Zhou

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