Dax for next quarterly due date

[s-roberts]

I have a loan start date of April 15th 2022 and have a quarterly payment. How do I calculate the number of days from today that's it's due each quarter. 
The report will need to show upcoming due dates and number of days (from today) that it's due.

Its not based on a calendar quarter The initial start date per loan can be any date. 

[Ashish_Mathur · Accepted solution]

Hi,

The EDATE() function shows the date a few months before or after the initial date specified.  If you are looking for some other solution, then share some data and show the expected result.

[v-xiaotang · Accepted solution]

Hi @s-roberts 

You can try this. Let's just take the first quarter as an example, because the principle of other quarters is the same.

create the measure

Measure = 
var _today=TODAY()
var _duration= CALCULATE(MAX('Table'[Duration (quarters)]),'Table'[loan ID]= MIN('loan list'[loan ID]))
var _loanstartDate=CALCULATE(MAX('Table'[loan start date]),'Table'[loan ID]= MIN('loan list'[loan ID]))
var _endDate= EDATE(_loanstartDate,_duration*3)

var _currentQ= DATEDIFF(_loanstartDate,_today,QUARTER)+1
var _currentdays= DATEDIFF(_today,EDATE(_loanstartDate,_currentQ*3),DAY)
var _currentEnd=EDATE(_loanstartDate,_currentQ*3)
return  "Currently in quarter"&_currentQ&UNICHAR(10)&"remaining days = "&_currentdays&UNICHAR(10)&"Current Due date: "&_currentEnd&UNICHAR(10)&"End Date: "&_endDate

result

Of course, you can split the statements as a single measure, just choose the way you like.

Best Regards,

Community Support Team _Tang

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

[Ashish_Mathur · Accepted solution]

Hi,

The EDATE() function shows the date a few months before or after the initial date specified.  If you are looking for some other solution, then share some data and show the expected result.