Forum Discussion
Dynamic calculation based on helper table and selected period
Hi, I have seen many posts and video related to my question, however nothing seems to be working as I expected, though I have tried several option and combinations. Hence seeking help herewith. Sce...
gmsamborn
Super User
Super UserHi saqwild
Would the following measures help?
Value = SUM( 'Extract'[Amount] )
Invoice Age =
VAR _SelectedPeriod = SELECTEDVALUE( 'Selection Period Table'[Selected Period] )
VAR _InvDt = MAX( 'Extract'[Trans Date] )
VAR _Result =
DATEDIFF(
_InvDt,
_SelectedPeriod,
DAY
)
RETURN
IF(
HASONEVALUE( 'Extract'[Trans Ref] ),
_Result,
""
)
Aging Group =
VAR _Age = [Invoice Age]
VAR _Result =
CALCULATE(
MAX( 'Buckets'[group] ),
'Buckets'[min] <= _Age
&& 'Buckets'[max] >= _Age
)
RETURN
IF(
HASONEVALUE( 'Extract'[Trans Ref] ),
_Result,
""
)
Pct =
VAR _Age = [Invoice Age]
VAR _Result =
CALCULATE(
MAX( 'Buckets'[Percent] ),
'Buckets'[min] <= _Age
&& 'Buckets'[max] >= _Age
)
RETURN
IF(
HASONEVALUE( 'Extract'[Trans Ref] ),
_Result,
""
)
New Value =
SUMX(
VALUES( 'Extract'[Trans Ref] ),
[Value] * ( 1 + [Pct] )
)
Let me know if you have any questions.
gmsamborn thank for your reply.
as i mentioned previsouly, my selected period is not the date but status
VAR _SelectedPeriod = SELECTEDVALUE( 'Selection Period Table'[Selected Period] ) this would be VAR _SelectedPeriod = SELECTEDVALUE( 'Selection Period Table'[Period Status] )at the same time i want to make the report dynamic based on Period status = Selected period.
I tried the above code an static date, all works fine excep, the date is hardcoded now and the total value of "New Value" is not correct.
