Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Norbertus
Helper V
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?

 

ContractAmountPeriodStartdateEnddate Resultjan-21feb-21mrt-21apr-21mei-21jun-21jul-21aug-21sep-21okt-21nov-21dec-21jan-22feb-22mrt-22apr-22
A €            100,00Year1-3-2021      €            100,00            €            100,00 
B €            150,00Quarter15-2-202131-12-2021    €            150,00   €            150,00   €            150,00   €            150,00     
C €              75,00Month13-1-202116-9-2021   €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00 €              75,00       
D €              85,00Month1-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

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
            ADDCOLUMNS(
                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:

image.png

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

image.png

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

View solution in original post

5 REPLIES 5
TomMartens
Super User
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
TomMartens
Super User
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

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
            ADDCOLUMNS(
                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:

image.png

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

image.png

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

hi @TomMartens 

It look great. Thanks a lot

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.