Cumulative sum of remaining months

[Dunner2020]

Hi there,

I have a table which looks like as follow:

This table is connected with a date table which has got RY Month number. April has RY month number =1 and Mar has RY month = 12. I want to calculate the cumulative sum of the value of those months who has not gone yet. It should look like as follow:

So I am creating measure something like as follow:

Unplanned forecast debug =

Var _CYMonth = MONTH(TODAY())

Var _RYMonth = if(_CYMonth > 3 && _CYMonth <= 12, _CYMonth - 3,_CYMonth + 9)

Var remain_month = SUM('Table'[Value])

Var remain_month_cum = SUMX(FILTER(ALL('Table'),Max('Table'[Month Number])>= _RYMonth),remain_month)

Return

IF(max(Dates[RY Month Number])>= _RYMonth,remain_month_cum ,BLANK() )

However, it does not produce the desired result. Could anyone help me where am I  making the mistake?

Link of file: https://1drv.ms/u/s!AucycxZHFe9TjUOV3GfsP_bL3FcC?e=LwTupY

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Dunner2020]

@Ashish_Mathur , I have attached dummy file in the post. Unfortunately, your calculation did not work in my file. 

[Ashish_Mathur]

Hi,

Your solution is not even remotely close to what i have suggested.  In your visual, Year and Month name have to be dragged from the Calendar Table.  Furthermore, your cumulative measure is nowhere close to mine.  Please study the file that i have shared with you, very carefully.

[CNENFRNL]

Hi, @Dunner2020 , as to your measure, an error lies in the misuse of variable in this part, I think.

Var remain_month = SUM('Table'[Value])
Var remain_month_cum = SUMX(FILTER(ALL(Dates),Dates[RY Month Number]>=_RYMonth),remain_month)

In fact, variables in DAX are NOT THAT VARIABLE as expected!

In DAX, variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

You might want to refer to an article on this subject for more details.

Without a complete data model, it's hard to debug to the fullest extent. If you attach a dummy file with enough mockup data, it's way much easier to troubleshoot.

[Dunner2020]

@CNENFRNL I have included the dummy file in the actual post. 

[CNENFRNL · Accepted solution]

@Dunner2020 , you might want to try this measure to achieve desired values,

Cumulative measure = 
VAR _CYMonth = MONTH ( TODAY () )
VAR _RYMonth = IF ( _CYMonth > 3, _CYMonth - 3, _CYMonth + 9 )
RETURN
    SUMX (
        FILTER (
            ALL ( '5y average' ),
            '5y average'[Month Number] <= MAX ( '5y average'[Month Number] )
                && '5y average'[Month Number] >= _RYMonth
        ),
        CALCULATE ( SUM ( '5y average'[Value] ) )
    )