Calculating the amount from a helper table.

[saqwild]

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.

[v-yohua-msft]

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:

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.

[saqwild]

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

[v-yohua-msft · Accepted solution]

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:

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.

[ryan_mayu]

@saqwild 

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?

[saqwild]

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%

Regads