cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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%

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.

1 ACCEPTED SOLUTION
Community Support

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]) * _Percent``````

Here is my preview:

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 5
Community Support

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] * _Percent
``````

Here is my preview:

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.

Frequent Visitor

Thank you yongkang for your time and effort but i would prefer doing this thru a measure, tbale direct calculaiton is musch easier though

Community Support

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]) * _Percent``````

Here is my preview:

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.

Super User

the extract table is the first table? base amount is the amount column?

which column is date calc?

The percent column is the expected output? what's the calculation logic?

Proud to be a Super User!

Frequent Visitor

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 max
so if an amount is within the bucket of 61 to 90 the the expected output must be all amounts within this grid * 40%

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors