Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
Proud to be a Super User!
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
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
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.
Proud to be a Super User!
thank you very much for that. Works like a charm. You made my day. Have a good one.
Regards
Björn
you are welcome
Proud to be a Super User!
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |