How to CALCULATE VAR table with USERELATIONSHIP

[ohnothimagain]

Hello everyone,
I have a table below,and want to sum [Qty] on variable table by cumulative and using inactive relationship
between [Calculated] and Calendar table.

Ftd_sum2:=
VAR tbl = 
    SELECTCOLUMNS(
        FILTER(ALL('TEST_'), 'TEST_'[STS] = "〇"),
        "@Trg", 'TEST_'[PN]
      )
VAR Dis =DISTINCT(tbl)   
VAR Ftd = 
    CALCULATETABLE(                                    
            'TEST_',filter(allselected('TEST_'),'TEST_'[PN] IN Dis),                                             
               USERELATIONSHIP('Calendar'[Date],'TEST_'[Calculated])
                    )  
VAR Result =
    CALCULATE(
        SUM('TEST_'[Qty]),
        KEEPFILTERS(Ftd),filter(allselected('Calendar'),
                     'Calendar'[Date]<=max('Calendar'[Date]))
              )
return 
Result

Result is all blank..Upon checking the operarion,it caused when I added  USERELATIONSHIP on CACULATETABLE.

This might be related to following article,I have no idea to do what I needed by using LOOKUPVALUE.
USERELATIONSHIP in calculated column
Could anyone teach me how to correct it on Excel powerpivot??

Best regards,

[sjoerdvn]

I don't know what's going on with all those filters and table calculations, but if you want to calculate a cummulative sum on that inactive date column, try:

Ftd_sum2:=
VAR md = MAX('Calendar'[date])
RETURN CALCULATE(
	SUM('TEST_'[Qty]),
	USERELATIONSHIP('Calendar'[Date],'TEST_'[Calculated]),
	ALL('Calendar'),
	'Calendar'[Date]<=md)
)

 If you need additional filters, apply them to the page or visual filters... 

[ohnothimagain]

I'm sorry for my late reply.Yes,your suggestion can success at most of cases.
the key point is it uses relationship between "calculated column" and calender.

>This might be related to following article,but I have no idea to do what I needed by using >LOOKUPVALUE.
>USERELATIONSHIP in calculated column

[jgeddes]

The following measure might help you or at least get you pointed in the right direction.

Cumulative Sum = 
var _tbl = 
	DISTINCT(
		SELECTCOLUMNS(
		FILTER(ALL(TEST_), TEST_[STS] = "〇"),
		"@Trg", TEST_[PN]
	)
)
var _result = 
CALCULATE(
	SUM(TEST_[Qty]),
    ALL('calendar'),
	FILTER(TEST_, TEST_[Calculated] <= MAX(calendar[Date]) && TEST_[PN] in _tbl),
	USERELATIONSHIP(TEST_[Calculated], calendar[Date])
)
RETURN
_result

[ohnothimagain]

Thank you for replying quickly! I tried right away,but result was unfortunately unsuccess.
-->putting userelationship on "calculated column" seems preventing

I've put this excel file on OneDrive,please teach me if you have more ideas.
OneDrive