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.
Scenario:
I have three tables as below.
1. Extract table, which contains my data, customer code, name, transaction reference, invoice data and amount
| Account Code | Account Name | Trans Date | Trans Ref | Amount |
| 150011 | ABC Co | 20/06/2024 | 2434578 | 6389 |
| 150011 | ABC Co | 23/07/2024 | 2434579 | 8876 |
| 150012 | XYZ Co | 23/01/2021 | 2166352 | 5566 |
| 150012 | XYZ Co | 15/04/2022 | 2226678 | 7744 |
2. Grid Table, containing matrix to calculate the amounts within threshold.
| order | group | min | max | Percent |
| 1 | 0-60 | 0 | 60 | 0% |
| 2 | 61-90 | 61 | 90 | 5% |
| 3 | 91-180 | 91 | 180 | 10% |
| 4 | 181-360 | 181 | 360 | 50% |
| 5 | 360+ | 361 | 99999999 | 100% |
3. Selection Period Table, to show current or forecast calculation
| Selected Period | Period Status |
| 31/07/2024 | Current |
| 31/08/2024 | Current +30 |
| 20/09/2024 | Current +90 |
Above table I have generated with MAX of Transaction Date from extract table + EDATE(+x)
PeriodTable =
VAR BDLastDate = FORMAT(max('Extract'[Transaction Date]),"dd/mm/yyyy")
VAR BDFCT30 = EDATE(BDLastDate,1)
VAR BDFCT60 = EDATE(BDLastDate,2)
VAR BDFCT90 = EDATE(BDLastDate,3)
RETURN
{(BDLastDate,"Current"),(BDFCT30,"Current +30"),(BDFCT60,"Current +60"),(BDFCT90,"Current +90")}
I have renamed the columns to "SelectedPeriod" and "Period Status". "Period Status" is my slicer filter
Requirement:
I want to achieve the following.
- generate invoice age based on slicer filter, if slicer filter is "Current" then based on last date of transaction period, "Current +30" would be last transaction date +30 days and so on (as above)
- generate calculated amount within each Grid group, what will fall under 0-30, 0-60,0-90 and so on. This will change if the slicer filter changed from one to another. i.e. if slicer changes from "Current" to "Current +30", accordingly the grid, age and calculation will change.
- generate allowance provision based of Grid min max group divided by percent. i.e. if an amount is withing 0-60 days group then it will calculate a provision of 0%, 61-90 days will be amount multiplied by 5% and so on. This also needs to be changed as and when the slicer filter changes.
Trust above clarify the scenario.
Thank you in advance for the time and effort.
8 Replies
Hello 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] * GroupPercentHope 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.
Hi 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.
