Cumulative sum from 2 different tables

[kayo]

I have a table with "Issue Open date" and "Issue Closed date"

I need to have cumulative open issue by week

If 2 issue raised in week 19 and 5 issue in week 21, but also 2 issue closed in week 21, then cumulative total for week 21 is 3

I duplicate table and got 2 cumulatives for issue raised, issue closed, but I cannot get Cululative(Sum) of those data

Below matrix, week 27 result should be 3 but I get -4

Measure I use for cumulative 

------

CumRaised =

CALCULATE (

    SUM ( Sheet1[CountRaised]),

    FILTER(

        ALL ( 'Sheet1' ),

        'Sheet1'[Raised date] <= MAX('Sheet1'[Raised date] )

  )

)

-------------------------

CumRaised =

CALCULATE (

    SUM ( Sheet1[CountRaised]),

    FILTER(

        ALL ( 'Sheet1' ),

        'Sheet1'[Raised date] <= MAX('Sheet1'[Raised date] )

  )

)

-------------To sum up above -----------

CalcOpenIssue = CALCULATE([CumRaised]-[CumClosed])

I try to use SUM instead of CALCULATE but it does not accept measure in calculation,

Can anyone help?

Thanks

[kayo]

I have created sample but I do not know how I can share in this forum

Is there way to attach PBI?

[tamerj1]

@kayo 

You can upload to any file transfer service and share the download link. 

[tamerj1]

@kayo 

I won't have have access to my PC for the coming hours. Would you like to connect via zoom or teams?

[tamerj1]

Hi @kayo 
Please try 

CalcOpenIssue =
VAR CurrentWeek =
    SELECTEDVALUE ( 'Sheet1'[WeekNum] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED ( 'Sheet1'[WeekNum] ),
        "@Raised", [CumRaised],
        "@Closed", [CumClosed]
    )
VAR T2 =
    FILTER ( T1, [WeekNum] <= CurrentWeek )
RETURN
    SUMX ( T2, [@Raised] - [@Closed] )

[kayo]

Thank you for your advice, however the result is the same.

Maybe I should explain that I use 3 tables for this measure

Table1. Original input to cumulate count column date "issue raised"

Table 2. copy of original table to cumulate count column date "issue closed" 

Table 3. Calendar date to create relation between Raised date and Closed date to get week summary

[tamerj1]

Hi @kayo 
It would be much better if you can prepare a sample pbix file to work with. Thank you.