Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a simple table of agents selling products - consisting of 5 columns.
The goal is to create a new column “# Months on position” which will calculate the number of months on a current remuneration level.
I you have a look at Agent1 from example – he started selling products on 1st Jan 2017, has not been promoted to higher remuneration level (type) so in report of January 2018 the number of months of cooperation should be “13”, in February 2018 “14” etc. In March and April 2018 he made no turnover and he is missing in the source data on those months, but the following month (June) he managed to sale some products, so he appeared in source data back again and the expected count of months should be “17”.
If you now look at Agent2 – he started selling products on 1st Jan 2018. The principle of month counting is the same as above, however Agent2 has been promoted to higher remuneration level “AG” in April 2018 and based on this the expected (desired) number of months in that row must be “1 “ (for May 2018 “2” etc.).
By Agent1 & Agent2 data appeared in time order, what is not a rule in source data (see Agent 3).
I am quite new to power BI and I will appreciate every help. I am totally helpless...
Thank you very much!
Solved! Go to Solution.
hi, @sportwatch
After my test, you may try to use this formula:
Result = DATEDIFF ( Table1[DAY], CALCULATE ( MIN ( Table1[Month] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) ) ), MONTH ) + 1 + DATEDIFF ( CALCULATE ( MIN ( Table1[Month] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) && Table1[TYPE] = EARLIER ( Table1[TYPE] ) ) ), Table1[Month], MONTH )
Result:
Best Regards,
Lin
Hi Lin,
I have tested your formula and... It works like a charm !!!!!!! Thanks a lot for your help 🙂
Kind regards,
Richard
hi, @sportwatch
You can use EARLIER Function to create a column as below:
Result = DATEDIFF(Table1[DAY],CALCULATE(MIN(Table1[Month]),FILTER(Table1,Table1[AGENT]=EARLIER(Table1[AGENT])&&Table1[TYPE]=EARLIER(Table1[TYPE]))),MONTH)+1+DATEDIFF(CALCULATE(MIN(Table1[Month]),FILTER(Table1,Table1[AGENT]=EARLIER(Table1[AGENT])&&Table1[TYPE]=EARLIER(Table1[TYPE]))),Table1[Month],MONTH)
and I want to know how to calculate the start “# Months on position”
for example :
if it is datediff (Day appointed , Month, month)+1, but why when Month is 2018/04 and Day is 2018-06-01 for Agent2 “# Months on position” is 1 in you sample data?
What is logic?
Best Regards,
Lin
Hi Lin,
I have just tested your solution - the first part (general numbering) works excellently 🙂 However the second part (reset counter to value "1" when the value in column "TYPE" is changed is still open. I would really highly appreciate your help on this....
Thanks a million.
Kind regards,
Richard
hi, @sportwatch
I notice that Why (Agent2, Month 2018/04) needs to "reset" the counter back to "1" but (Agent3, Month 2018/05) does not need to?
Reset or not in the formula is that whether to add this condition or not "&&Table1[TYPE]=EARLIER(Table1[TYPE])".
If it is not your case, please share some data sample and expected output. Check the sample data for completeness with the expected output logic。
Best Regards,
Lin
Hi Lin,
I apologize for my mistake... You are right, the value in calculated column by Agent3, Month 2018/05 must be reset back to "1". I am sorry I have made a mistake in the example. Do you think this is solvable?
I attach the corrected values for Agent3 in the calculated column.
Thank you again so much!
Kind regrds,
Richard
MONTH | AGENT | AGENT ID | DAY APPOINTED | TYPE | # Months on position |
2018/02 | Agent 3 | 57222 | 01.02.2018 | AN | 1 |
2018/05 | Agent 3 | 57222 | 01.02.2018 | AG | 1 |
2018/06 | Agent 3 | 57222 | 01.02.2018 | AG | 2 |
2018/08 | Agent 3 | 57222 | 01.02.2018 | AG | 4 |
2018/03 | Agent 3 | 57222 | 01.02.2018 | AN | 2 |
2018/04 | Agent 3 | 57222 | 01.02.2018 | AN | 3 |
hi, @sportwatch
After my test, you may try to use this formula:
Result = DATEDIFF ( Table1[DAY], CALCULATE ( MIN ( Table1[Month] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) ) ), MONTH ) + 1 + DATEDIFF ( CALCULATE ( MIN ( Table1[Month] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) && Table1[TYPE] = EARLIER ( Table1[TYPE] ) ) ), Table1[Month], MONTH )
Result:
Best Regards,
Lin
HI Lin,
after detailed testing I came across some incorrect results unfortunately.
I am attaching a data set which returns incorrect values. The first incorrect value is highlited in a blue circle. The expected value ther is "1", not "11(the counter has been reset to "11" instead of "1")
May I kindly ask you to have a look at it?
Thanks a lot.
Kind regards,
Richard
hi, @sportwatch
Reason for (Agent4, Month 2017/04) result is 11 is that My computational logic for the starting value of Agent4 is 11 ( datediff([day],min[Month],month)+1=(2017/1-2016/3)+1=11 )
So the reset the value of (Agent4, Month 2017/04) is 11 not 1.
Best Regards,
Lin
Hi Lin,
thank you very much for your reply. I understand your point, however when a change in column "TYPE " occurs the counter must be ALWAS reset to "1" not to the MIN value which has been found in previous months.
Thanks a lot for your help.
Kind regards,
Richard
hi, @sportwatch
Sorry for the delay reply, after my test, you may try to this formula
New Result = IF ( Table1[TYPE] = CALCULATE ( MAX ( Table1[TYPE] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) ) ), DATEDIFF ( Table1[DAY], CALCULATE ( MIN ( Table1[Month] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) ) ), MONTH ) + 1, 1 ) + DATEDIFF ( CALCULATE ( MIN ( Table1[Month] ), FILTER ( Table1, Table1[AGENT] = EARLIER ( Table1[AGENT] ) && Table1[TYPE] = EARLIER ( Table1[TYPE] ) ) ), Table1[Month], MONTH )
Result:
Best Regards,
Lin
Hi Lin,
thank you so much for your help. Your formula works almost excellently and we are aproaching to excellence 🙂
The incorrect results are returned when there is a change in a column "DAY APPOINTED" by particular agent. It happens when an agent quits cooperation with our company but then, after a period of time he change his mind and starts to cooperate with us again. In this case the "DAY APPOINTED" value is changed in source database (historical data are left unchanged).
Your formula however by those cases shows incorrect value "1" in first row of entries for such agent (the rest of values are then incorrect as well, as they take the number "1" as a first value). The correct value is (MONTH - DAY APPOINTED )+1
You can see this in attachment, I have added a column with results given by your formula and one with desired (correct values) .
Please excuse me, I know I am hassling but to solve this issue is beyond my knowledge of DAX for now...
I am very thankfull for your help.
Kind regards,
Richard
Hi Lin,
I have tested your formula and... It works like a charm !!!!!!! Thanks a lot for your help 🙂
Kind regards,
Richard
Hi Lin,
thanks a lot for your effort 🙂
In the case you are mentioning (Agent2, Month 2018/04) the expected (desired) result is "1" instead of "11" because in month 2018/04 occured a change in the column "TYPE" (agent has been promoted, change from "AN" in previous month to "AG in current month") an this event must "reset" the counter back to "1". (we have about 30 values which might appear as a "TYPE", not only those two, each change must trigger "reset of a counter")
I have just notice that in the screenshot from Power BI you sent me there is an error in source data by Agent2 on 1/1/2018. The "DAY APPOINTED" date is still the same - the correct value in this row should be "6/1/2017".
Thanks a lot!!!!!!
Kind regards,
Richard
@sportwatch Thanks for detailed explanation of the scenario. Could you please post the same sample data in Copiable format (just to make life easier)
Proud to be a PBI Community Champion
Hi pattemmanohar,
Thanks a lot for your reply, I am sending the data (txt, I do not know how to enclose a sheet). Btw I spent about a day to find out how to solve this in PowerBI - nothing...
Again - thank you.
MONTH AGENT AGENT ID DAY APPOINTED TYPE # Months on position
2018/01 Agent 1 12580 01.01.2017 AG 13
2018/02 Agent 1 12580 01.01.2017 AG 14
2018/05 Agent 1 12580 01.01.2017 AG 17
2018/06 Agent 1 12580 01.01.2017 AG 18
2018/07 Agent 1 12580 01.01.2017 AG 19
2018/08 Agent 1 12580 01.01.2017 AG 20
2018/01 Agent 2 13610 01.01.2018 AN 1
2018/02 Agent 2 13610 01.01.2018 AN 2
2018/03 Agent 2 13610 01.01.2018 AN 3
2018/04 Agent 2 13610 01.01.2018 AG 1
2018/05 Agent 2 13610 01.01.2018 AG 2
2018/06 Agent 2 13610 01.01.2018 AG 3
2018/08 Agent 2 13610 01.01.2018 AG 5
2018/02 Agent 3 57222 01.02.2018 AN 1
2018/05 Agent 3 57222 01.02.2018 AG 4
2018/06 Agent 3 57222 01.02.2018 AG 5
2018/08 Agent 3 57222 01.02.2018 AG 7
2018/03 Agent 3 57222 01.02.2018 AN 2
2018/04 Agent 3 57222 01.02.2018 AN 3
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |