Months / Years in Contract

[BjoernSchaefer]

Hello everybody,

i'm dealing with a challenge right now. I have one table that looks like this

What i need is this:

I really don't know how to realize it with PowerQuery or DAX.

Can anyone help?

Thanks in advance

Björn

[Anonymous]

Hi @BjoernSchaefer ,

How do you want the Months in Contract and Years in contract to be reflected.  Is it with reference to current date ?

WHat is the business rule to be applied ?

Cheers

CheenuSing

[BjoernSchaefer]

Hi @Anonymous ,

thank you for your message. If a contract starts at 01.05.2017 then the next 12 months are the first year of the contract. So for the date values starting at 01.05.2017 and going to 01.04.2018 it is year one of the contract. For the next years the same procedure.

The values are invoice-rates. Each month we bill the customer. Another table in the model contains calculated costs depending on runtime of the contract. So, let's say, a contract is set for 54 months and we bill 120 € each month. In the first year we calculate with 1.25 % costs each month, in the second year 1.84% and so on.

Of course there's also something special to handle. Most customers get a lil' refund, mostly in November or December, for whatever reason. Important is, that for that months, the month-number has to be the same.

Do you get the concept? Sorry for my bad english, not my native language. And even in my native language it's hard to explain.

Regards

Björn

[ryan_mayu · Accepted solution]

@BjoernSchaefer 

you can try to use below DAX to create two columns for year and month. However, I can't get your explaination of value column.

month = 
VAR _start=minx(FILTER('Table (2)','Table (2)'[contract]=EARLIER('Table (2)'[contract])),'Table (2)'[Date_invoice])
VAR _month=DATEDIFF(_start,'Table (2)'[Date_invoice],MONTH)+1
return if(_month>12,if(mod(_month,12)=0,12,mod(_month,12)),_month)

year = 
VAR _start=minx(FILTER('Table (2)','Table (2)'[contract]=EARLIER('Table (2)'[contract])),'Table (2)'[Date_invoice])
VAR _month=DATEDIFF(_start,'Table (2)'[Date_invoice],MONTH)+1
return if(mod(_month,12)=0,int(DIVIDE(_month,12)),int(DIVIDE(_month,12))+1)

Btw, it will be better if you can share your sample data.

[BjoernSchaefer]

@ryan_mayu,

thank you very much for that. Works like a charm. You made my day. Have a good one.

Regards

Björn

[ryan_mayu]

@BjoernSchaefer 

you are welcome