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...
Sahir_Maharaj
Super User
Super UserHello saqwild,
Can you please try the following approach:
1. Calculate the difference between the transaction date and the selected period
Invoice Age =
VAR SelectedPeriod = MAX('Selection Period Table'[Selected Period]) -- Get the selected period from the slicer
RETURN
DATEDIFF('Extract'[Trans Date], SelectedPeriod, DAY)
2. Classify the invoices
Amount By Group =
VAR InvoiceAge = [Invoice Age] -- Use the invoice age calculated above
RETURN
CALCULATE(
SUM('Extract'[Amount]),
FILTER(
'Grid Table',
InvoiceAge >= 'Grid Table'[min] && InvoiceAge <= 'Grid Table'[max]
)
)
3. Calculate the provision based on the thresholds and percentages
Provision Amount =
VAR InvoiceAge = [Invoice Age] -- Use the invoice age calculated earlier
VAR GroupPercent =
CALCULATE(
MAX('Grid Table'[Percent]),
FILTER(
'Grid Table',
InvoiceAge >= 'Grid Table'[min] && InvoiceAge <= 'Grid Table'[max]
)
)
RETURN
[Amount By Group] * GroupPercent
Hope this helps!
Thank you Sahir,
As mentiond above, my slicer is not the date but "Period Status", so i cant directly calculate the datedif.
Let me try with a switch and see if it works based on max and edate
I can not refer the column name directly in the measure hence i tried the following
Invoice Age = VAR _CurrentPeriod = EOMONTH(MAX('Extract'[Transaction Date]),0) VAR _ListDates = SELECTEDVALUE('Extract'[Transaction Date]) VAR _CurrentAge = DATEDIFF(_ListDates, _CurrentPeriod, DAY) RETURN _CurrentAgebut its giving the weired numebrs, not matching when i calculate manually.
