Forum Discussion
Helper IIMonths / Years in Contract
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.
Helper IIHi 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
Super Useryou 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
thank you very much for that. Works like a charm. You made my day. Have a good one.
Regards
Björn
- ryan_mayu
you are welcome