Dax for Calculation of Total Monthly Rent= (Regular Rent of the month +Advance Paid in Previous Mont

[Raja_Rizwann]

Dear Dax Community,
Greeting to all.
I am facing a problem in Calculation of My Total Rent payment for the Month in my rent payment Table . Table is maintained in Excel and i am attaching the table. I want to dynamically calculate the Total Monthly Rent (Monthly Allocation+Advance Rent Paid in previous Month that belong to or fall in current month).

[speedramps]

I have loaded you data into PBIX on Onedrive

Click here 

Your expalanation is too vague.

Please ask you manager, teacher, parents or friend to help.

You will get a quick response if you put time, care and effort into writing clear problem descriptions with example data. Vague questions may waste you time and helpers time.

Look forward to helping you when the above information is forthcoming.

[speedramps]

We want to help you but your description is too vague. Please write it again clearly.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confusion.

Explain how the 67666.67 is calculated in plain language (do not copy and paste DAX or Excel formula)
 
Also provide the example desired output, with a clear step-by-step description of calculations the process flow.
Remember not to share private data ... we don't want you to get into trouble. ‌‌
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions with example data.

Look forward to helping you when the above information is forthcoming

a

[Raja_Rizwann]

ok Sir noted your instruction.  i am copy pasting Table . This is sample table not real data.
In the table i need the Total Rent Payment in last column . Critaria is if cover month is more than 1 than un amortized rent or advance rent that falls in next month should be added withe regular monthly allocaton  r. 

simply sum of  regular monthly allocation plus advance paid in last month based on critaria if cover month is more than 1 and Due date and Next month date (YYYY-MMM) should be same

Prop ID	Due Date	Total Rent Paid	Cover Months	Monthly Allocaton	Next Mont Date	Next Month Monthly rent	Total Rent in Next Moth
P-01	15-Jan-24	225500	2	112750	15-Feb-24	112750
P-01	15-Mar-24	225500	2	112750	15-Apr-24	112750
P-01	15-May-24	225500	2	112750	15-Jun-24	112750
P-01	15-Jul-24	225500	2	112750	15-Aug-24	112750
P-01	15-Sep-24	225500	2	112750	15-Oct-24	112750
P-01	15-Nov-24	225500	1	225500	15-Dec-24	0
P-01	05-Feb-24	135333	2	67666.5	05-Mar-24	67666.5
P-01	05-Apr-24	135333	2	67666.5	05-May-24	67666.5
P-01	05-Jun-24	135333	2	67666.5	05-Jul-24	67666.5
P-01	05-Aug-24	135333	2	67666.5	05-Sep-24	67666.5
P-01	05-Oct-24	135333	2	67666.5	05-Nov-24	67666.5
P-01	05-Dec-24	135333	1	135333	05-Jan-25	0

[Anonymous · Accepted solution]

Hi @Raja_Rizwann 

Did the following output is the result you want?

Total Rent in Next Moth =
VAR a =
    EOMONTH ( 'Table'[Next Mont Date], 0 )
VAR b =
    MAXX (
        FILTER (
            'Table',
            EOMONTH ( [Due Date], 0 ) = a
                && [Prop ID] = EARLIER ( 'Table'[Prop ID] )
        ),
        [Monthly Allocaton]
    )
RETURN
    [Next Month Monthly rent] + b

Best Regards!

Yolo Zhu

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