Forum Discussion

saqwild's avatar
saqwild
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

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's avatar
    saqwild
    Frequent Visitor
    2 years ago

    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