Forum Discussion

BjoernSchaefer's avatar
BjoernSchaefer
Icon for Helper II rankHelper II
5 years ago
Solved

Months / Years in Contract

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

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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.

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      BjoernSchaefer
      Icon for Helper II rankHelper II
      5 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

        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.