Forum Discussion
Calculating the amount from a helper table.
Hello Everyone.
The code I borrwed from This video.
I have two tables now, one with my master datad as following.
| Account Number | Account Name | Transaction Date | Description | Amount |
| 11111 | Test one | 20/06/2024 | Serial one | 555 |
| 2222 | Test two | 16/07/2024 | Serio Two | 999 |
then 2nd the helper table
| Group | order | Min | Max | Percent |
| 0-60 days | 1 | 0 | 60 | 20% |
| 61-90 days | 2 | 61 | 90 | 40% |
| 91-120 days | 3 | 91 | 120 | 50% |
I have just added an addition column "percent" to existing.
following below code
outstanding =
CALCULATE(SUM('Extract'[Amount]),
FILTER('Extract',
COUNTROWS(
FILTER('Grid',
[Date Calc]>='Grid'[Min]&&
[Date Calc]< 'Grid'[Max]))))
I want to calculate the "base amount" of each row with respective grid percent, based on the grid critera met.
any help on this will be highly appreciated.
Hi, saqwild
If you want to use measure. you can try the following DAX:
Base Amount with Percent measure = VAR DaysDiff = MAX('Master'[Days]) VAR _Percent = CALCULATE ( MAX ( 'Grid'[Percent] ), FILTER ( 'Grid', DaysDiff >= 'Grid'[Min] && DaysDiff <= 'Grid'[Max] ) ) RETURN MAX('Master'[Amount]) * _PercentHere is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Applogize Ryan for the ambiguity, i have corrected the code above.
the extract table is the first table? base amount is the amount column?
--- yes, extract is the first table and the amount is amount to calcualted
which column is date calc?
-- is the measure to as following
Measure = SELECTEDVALUE('Calendar'[Date]) Date Calc = SUMX('Extract', DATEDIFF('Extract'[Transaction Date], [Measure], DAY))The percent column is the expected output? what's the calculation logic?
-- yes and the logic is to get the percent of amount * grid percent based on ageing of min and maxso if an amount is within the bucket of 61 to 90 the the expected output must be all amounts within this grid * 40%Regads
Hi, saqwild
Create a calculated column to calculate the date variance
Days = DATEDIFF('Master'[Transaction Date], TODAY(), DAY)Then create another calculated column and try the following DAX Expression:
Base Amount with Percent = VAR DaysDiff = 'Master'[Days] VAR _Percent = CALCULATE ( MAX ( 'Grid'[Percent] ), FILTER ( 'Grid', DaysDiff >= 'Grid'[Min] && DaysDiff <= 'Grid'[Max] ) ) RETURN 'Master'[Amount] * _PercentHere is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you yongkang for your time and effort but i would prefer doing this thru a measure, tbale direct calculaiton is musch easier though
Hi, saqwild
If you want to use measure. you can try the following DAX:
Base Amount with Percent measure = VAR DaysDiff = MAX('Master'[Days]) VAR _Percent = CALCULATE ( MAX ( 'Grid'[Percent] ), FILTER ( 'Grid', DaysDiff >= 'Grid'[Min] && DaysDiff <= 'Grid'[Max] ) ) RETURN MAX('Master'[Amount]) * _PercentHere is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
