cancel
Showing results for
Did you mean:
Helper V

## Future payments per contract and periodtype

Hi,

A Contract has a Startdate, Enddate and a period how many times there is a payment (year, quarter, month)

I want to know when a future payment is expected.

Somebody a fantastic idea to solve this question?

 Contract Amount Period Startdate Enddate Result jan-21 feb-21 mrt-21 apr-21 mei-21 jun-21 jul-21 aug-21 sep-21 okt-21 nov-21 dec-21 jan-22 feb-22 mrt-22 apr-22 A €            100,00 Year 1-3-2021 €            100,00 €            100,00 B €            150,00 Quarter 15-2-2021 31-12-2021 €            150,00 €            150,00 €            150,00 €            150,00 C €              75,00 Month 13-1-2021 16-9-2021 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 D €              85,00 Month 1-5-2021 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00 €              85,00

I'm very curious

with kind regards

1 ACCEPTED SOLUTION
Super User

Hey @Norbertus ,

I use this DAX statement to create a table

``````Fact Projection =
var CalendarMax = MAX( 'Calendar'[Date] )
var fact =
SELECTCOLUMNS(
GENERATE(
'Billing'
, var ContractStart = 'Billing'[Begindate]
var ContractEnd = IF(ISBLANK('Billing'[Untildate]) , CalendarMax , 'Billing'[Untildate] )
var MonthUntilContractEnd = DATEDIFF( ContractStart , ContractEnd , MONTH )
var NoOfMonth =
SWITCH(
'Billing'[Period]
, "Month" , 1
, "Quarter" , 3
, "Year" , 12
)
return
GENERATESERIES( 1 , TRUNC( DIVIDE( MonthUntilContractEnd , NoOfMonth ) , 0) + 1 , 1 )
, "Date Projected" , DATE( YEAR( [Begindate] ) , MONTH( 'Billing'[Begindate] ) + ( [Value] - 1 ) * NoOfMonth , DAY( 'Billing'[Begindate] ) )
)
)
, "Contrac" , [Contract]
, "Amount" , [Amount]
, "Date Projected" , [Date Projected]
)
return
fact``````

This is how the relationships look like I created based on your sample file:

And this is how a matrix visual looks like

• Contract from the Billing table on rows
• Year and Month from the Calendar table on columns
• Amount from the new fact table as Values

How it works
I create a table "Fact Projection" based on information of the billing table, the number of rows per contract is determined by the period of the contract and the end of the contract or if the end of the contract is missing by the end of the calendar. Basically the number of months is determined by an integer division, the number of months divided by the monhts that are forming the period.

Then I'm using this information to "calculate" the projection date. Very helpful is the following DAX

``Date( 2021 , 1 + 12 , 11 )``

Creates the date of the 11th of January 2022.

Hopefully this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
5 REPLIES 5
Super User

Hey @Norbertus ,

here you will find a blog that describes  my solution wih some more words:

Events and projections - Mincing Data - Gain Insight from Data (minceddata.info)

and here I create the additonal fact table using Power Query and some hacky M:

Events and projections, using M - Mincing Data - Gain Insight from Data (minceddata.info)

Maybe you will find this interesting.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

Hey @Norbertus ,

please prepare a pbix file with sample data, that still reflects your data model, at least the table that holds the contract information. Upload the file to onedrive or dropbox and share the link. If you are using an xlsx to prepare the sample data and share the link.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper V
Super User

Hey @Norbertus ,

I use this DAX statement to create a table

``````Fact Projection =
var CalendarMax = MAX( 'Calendar'[Date] )
var fact =
SELECTCOLUMNS(
GENERATE(
'Billing'
, var ContractStart = 'Billing'[Begindate]
var ContractEnd = IF(ISBLANK('Billing'[Untildate]) , CalendarMax , 'Billing'[Untildate] )
var MonthUntilContractEnd = DATEDIFF( ContractStart , ContractEnd , MONTH )
var NoOfMonth =
SWITCH(
'Billing'[Period]
, "Month" , 1
, "Quarter" , 3
, "Year" , 12
)
return
GENERATESERIES( 1 , TRUNC( DIVIDE( MonthUntilContractEnd , NoOfMonth ) , 0) + 1 , 1 )
, "Date Projected" , DATE( YEAR( [Begindate] ) , MONTH( 'Billing'[Begindate] ) + ( [Value] - 1 ) * NoOfMonth , DAY( 'Billing'[Begindate] ) )
)
)
, "Contrac" , [Contract]
, "Amount" , [Amount]
, "Date Projected" , [Date Projected]
)
return
fact``````

This is how the relationships look like I created based on your sample file:

And this is how a matrix visual looks like

• Contract from the Billing table on rows
• Year and Month from the Calendar table on columns
• Amount from the new fact table as Values

How it works
I create a table "Fact Projection" based on information of the billing table, the number of rows per contract is determined by the period of the contract and the end of the contract or if the end of the contract is missing by the end of the calendar. Basically the number of months is determined by an integer division, the number of months divided by the monhts that are forming the period.

Then I'm using this information to "calculate" the projection date. Very helpful is the following DAX

``Date( 2021 , 1 + 12 , 11 )``

Creates the date of the 11th of January 2022.

Hopefully this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper V

It look great. Thanks a lot

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors