POWER BI SUBSTRACT FROM THE NEW COLUMN BEFORE RECORD

[viktorastzallas]

Hi ,

I have this table in power bi . Each person has lines for each month passes . 5 months equals to 5 appearances in the list. for each line and the number of days ( between START AND END column) i want to substract from the MONEY column the MONEY PER MONTH COLUMN and keep the differnce .After this differnce to be substracted again with the column MONEY PER MONTH from this row until i have 0 . The only catch is that i want if the difference( happens a lot of times at month 5 out of 5 ) if the MONEY PER MONTH column is less than 20 , i want it to be 20 and the before result 240. After the last month when a new name record comes the money again to be 1040

BASICALLY I WANT TO FORM THE RED LINED COLUMN IN IMAGE BELOW

[v-jianboli-msft]

Hi @viktorastzallas 

Please consider about using IF function. IF function (DAX) - DAX | Microsoft Learn

This question is beyond the topic of the post. You may consider opening a new thread with the background of the calculation and the expected output. This will make the post more targeted to better help you and others in need Thanks in advance!

Reference:

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @viktorastzallas ,

Please try:

DIFFERENCE =
VAR _a =
    CALCULATE (
        SUM ( 'Table'[MONEY PER MONTH] ),
        FILTER (
            'Table',
            [ID] = EARLIER ( 'Table'[ID] )
                && [Index] < EARLIER ( 'Table'[Index] )
        )
    )
VAR _b = [MONEY] - _a
VAR _c =
    CALCULATE (
        SUM ( 'Table'[MONEY PER MONTH] ),
        FILTER (
            'Table',
            [ID] = EARLIER ( 'Table'[ID] )
                && [Index]
                    <= EARLIER ( 'Table'[Index] ) + 1
        )
    )
VAR _d =
    CALCULATE (
        SUM ( 'Table'[MONEY PER MONTH] ),
        FILTER (
            'Table',
            [ID] = EARLIER ( 'Table'[ID] )
                && [Index] <= EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    SWITCH ( TRUE (), [MONEY] - _d = 0, 20, [MONEY] - _c = 0, 240, _b )

Final output:

Best Regards,

Jianbo Li

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

[viktorastzallas]

I need to ask u something that refers to my previous question  , to add something extra .260  * DATEDIFF('TABLE'[START],'TABLE'[END]+1,DAY)/DAY( EOMONTH( [END], 0 ) ) . This is the type i use to calculate the money per month(260 FOR A FULL MONTH WITH 30 OR 31 DAYS) . The total money for each ID is 1040 and must not be exceeded . But when i use this type if there is one extra day the money per month is 8 . The minimum money per month must be at least 20 and have to been deducted from the money per month from the above money per month . Thank you a lot for your time ! Its from this

[viktorastzallas]

hi and thanks for the answer . They removed my image and i re uploaded it . 

[v-jianboli-msft]

Hi @viktorastzallas ,

Sorry for that the information you have provided is not making the problem clear to me. I can't find your image. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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