Forum Discussion

ohnothimagain's avatar
ohnothimagain
Icon for Helper I rankHelper I
2 years ago

How to CALCULATE VAR table with USERELATIONSHIP

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,

4 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      ohnothimagain
      Icon for Helper I rankHelper I
      2 years ago

      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 

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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's avatar
      ohnothimagain
      Icon for Helper I rankHelper I
      2 years ago

      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