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 = ...
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