Forum Discussion

PowerBigginer's avatar
PowerBigginer
Helper II
2 years ago
Solved

Dynamic Calcultion

Hi Team I need help on Dax
If contract paid extra amount than due amount that extra amount should go for Next month
Sample Data

ContractNum   Due     Paid        Date
11220500148   1000    1000      4/1/2024
11220600831   2000    4000      4/1/2024
11220900717   500     1000        4/1/2024
11230200276   500     500          4/1/2024
11220500148   1000     0            5/1/2024
11220600831   2000     0            5/1/2024
11220900717   500      0              5/1/2024
11230200276   500       0              5/1/2024

If you see contract number 11220600831 paid 4000 but due amount is 2000 in this case expected result should be 2000 in Apr month (4/1/2024) and remaing 2000 should show (5/1/2024). you can see below expected result


Expected Result


ContractNum        Due       Paid      Date
11220500148        1000      1000     4/1/2024
11220600831        2000      2000     4/1/2024
11220900717        500        500       4/1/2024
11230200276        500        500       4/1/2024
11220500148        1000      0           5/1/2024
11220600831        2000      2000     5/1/2024
11220900717        500        500       5/1/2024
11230200276        500        0           5/1/2024

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PowerBigginer ,

     

    1. Create a calculated column to get the remaining value.

     

    Column =
VAR _remaind = 'Table'[Paid] - 'Table'[Due]
VAR _min_date =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[ContractNum ] = EARLIER ( 'Table'[ContractNum ] )
)
)
RETURN
IF (
'Table'[Date] = _min_date, _remaind, _remaind, 'Table'[ContractNum ] ) )
_remaind.
IF (
'Table'[Date] = _min_date, _remaind, IF (
'Table'[Paid], _remaind, IF (
IF ( DATEADD ( 'Table'[Date], -1, MONTH ) = _min_date, 9999 )
)
)

     

    2. Create a calculated column and put the value into the next month.

     

    Column 2 =
VAR _res =
CALCULATE (
MAX ( 'Table'[Column] ),
FILTER (
ALL ( 'Table' ), DATEADD ( 'Table'[Date], +1, MONTH ) = E
DATEADD ( 'Table'[Date], +1, MONTH ) = EARLIER ( 'Table'[Date] )
&& 'Table'[ContractNum ] = EARLIER ( 'Table'[ContractNum ] )
)
)
RETURN
IF ( 'Table'[Column] = 9999, _res, 'Table'[Due] )

     

    3. Click the small eye next to the column name to hide the unwanted columns.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PowerBigginer ,

     

    1. Create a calculated column to get the remaining value.

     

    Column =
VAR _remaind = 'Table'[Paid] - 'Table'[Due]
VAR _min_date =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[ContractNum ] = EARLIER ( 'Table'[ContractNum ] )
)
)
RETURN
IF (
'Table'[Date] = _min_date, _remaind, _remaind, 'Table'[ContractNum ] ) )
_remaind.
IF (
'Table'[Date] = _min_date, _remaind, IF (
'Table'[Paid], _remaind, IF (
IF ( DATEADD ( 'Table'[Date], -1, MONTH ) = _min_date, 9999 )
)
)

     

    2. Create a calculated column and put the value into the next month.

     

    Column 2 =
VAR _res =
CALCULATE (
MAX ( 'Table'[Column] ),
FILTER (
ALL ( 'Table' ), DATEADD ( 'Table'[Date], +1, MONTH ) = E
DATEADD ( 'Table'[Date], +1, MONTH ) = EARLIER ( 'Table'[Date] )
&& 'Table'[ContractNum ] = EARLIER ( 'Table'[ContractNum ] )
)
)
RETURN
IF ( 'Table'[Column] = 9999, _res, 'Table'[Due] )

     

    3. Click the small eye next to the column name to hide the unwanted columns.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.