12 Months forward rolling sum aggregated by first of each month

[powerbi_Mandal]

Hi,

I have a formula that works very well when I have a full list of dates, but I need to reduce the size and only see results by each month.

The data is coming from a repayment schedule where amounts repaid can occur at any date, so I need for my formula to still sum all 365 days ahead even though I only want to see the effect by each first date of the month.

Short term debt first each month = CALCULATE(
    SUM( factSecurity[amount_instalment]) + 
    SUM( factSecurity[Cash sweep company]) +
    SUM( factSecurity[Cash sweep facility]) +
    SUM( factSecurity[Cash sweep standstill]) +
    SUM( factSecurity[Converted to equity]) +
    SUM( factSecurity[Merged from standstill tranches]) +
    SUM( factSecurity[Adjustments]),  DATESBETWEEN(factSecurity[Dates],MAX(factSecurity[Dates])+1, Edate(Max(factSecurity[Dates]),12))) 
* SUMX(factSecurity, factSecurity[Currency Rate])

Anyone who knows how to make this work when I remove the complete list of dates and replace it with only the first date each month?

Best regared
Espen 

[amitchandak · Accepted solution]

@powerbi_Mandal , You should always use date table marked as date table, joined with your date

Try like

Short term debt first each month = CALCULATE(
SUMX( factSecurity, (factSecurity[amount_instalment] +factSecurity[Cash sweep company] + factSecurity[Cash sweep facility] + factSecurity[Cash sweep standstill]+ factSecurity[Converted to equity] + factSecurity[Merged from standstill tranches] + factSecurity[Adjustments]) *factSecurity[Currency Rate]), DATESINPERIOD('Date'[Date],MAX('Date'[Date]),12,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[powerbi_Mandal]

Thanks 🙂 This was very helpful!