Cumulate measure in matrix

[JorgeAbiad]

Hello PBI Experts,

I have posted earlier and got some replies. Unfortunately, I'm still not getting the expected results.

I'm now providing some more information.

From the Matrix visual above, i have the following measures:

CDP Current_tbl = CALCULATE(SUM(CDP_Sheet[CDP Released Revenue]))

Current (RVO+CDP)_tbl = if(not(ISBLANK('RVO1-3_Sheet'[RVO])), 'RVO1-3_Sheet'[RVO], CDP_Sheet[CDP Current_tbl])

   --where RVO: RVO = CALCULATE(SUM('RVO1-3_Sheet'[AVG YTD FX])/1000)

The measure Current (RVO+CDP)_tbl is a combination of two measures from two different tables as expressed by the formula. Both of these tables have date column and another column name "Month" based on the date column.

The date column is linked to a Calendar table.

My requirement is to get the cumulative value of Current (RVO+CDP)_tbl using another measure.

I've been stuck on this for several days now.

I've tried using the measure below but it only gives the cumulative values for Jan, Feb March.

RVO-CDP-Cum_tbl = SUMX(FILTER(ALL('RVO1-3_Sheet'), 'RVO1-3_Sheet'[Month]<=MAX('RVO1-3_Sheet'[Month])), 'RVO1-3_Sheet'[Current (RVO+CDP)_tbl])

 The above measure only adds the values from RVO. It stopped adding from the column Apr-Dec.

Please help me.

Regards,

JorgeAbiad

[V-pazhen-msft]

@JorgeAbiad 

This is because you filtered with the date column(only Jan to March) in the ROV1-3 sheet, try filter with the Calendar Table. Try change the cumulative measure to something like:

RVO-CDP-Cum_tbl = SUMX(FILTER(ALL('CalendarTable'), 'CalendarTable'[Monthnumber]<=MAX('CalendarTable'[Monthnumber])), 'RVO1-3_Sheet'[Current (RVO+CDP)_tbl])

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JorgeAbiad]

Hello @V-pazhen-msft ,

Your response made my day! You are an angel sent down to help me solve my issue. Thouh I never realized that the solution was just in front of me.

Thank you very much:)

[Greg_Deckler]

This is really hard to troubleshoot without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Based upon your RVO column, it looks for all the world like your MAX('RVO1-3_Sheet'[Month])) is equaling out to 3, which may make some amount of sense based on the name of that table. And hence that would be the reason your cumulative sum stops. I would recommend that if you want the calculation to continue that you don't filter things out based upon that particular column in that particular table but pick a different one. Not sure I can be too specific past that since see previous comments above about not having any idea about your source data layout, etc.

[JorgeAbiad]

Hi @Greg_Deckler ,

 Please get my pbix file here

My Measures:

Current (RVO+CDP)_tbl = if(not(ISBLANK('RVO1-3_Sheet'[RVO])), 'RVO1-3_Sheet'[RVO], CDP_Sheet[CDP Current_tbl])

****Jan-Mar columns are filled by values from RVO, while Apr-Dec values are taken from CDP Current_tbl measure

The requirement is to cumulate Current (RVO+CDP)_tbl

As stated in the post, only values from Jan-Mar are being cumulated.(Jan-1522062;Feb-2884759;Mar-4089281) . These are correct. Novalues are displayed for Apr-Dec.

Please help.

Regards,

JorgeAbiad