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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BjoernSchaefer
Helper II
Helper II

Months / Years in Contract

Hello everybody,

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

What i need is this:

expected outcome.png

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

 

Can anyone help?

 

Thanks in advance

 

Björn

1 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)

1.png

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
CheenuSing
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing ,

 

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

@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)

1.png

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu,

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

 

Regards

 

Björn

@BjoernSchaefer 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.