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
sportwatch
Frequent Visitor

Calculation of months with variables

Hi,

I have a simple table of agents selling products - consisting of 5 columns.

  • Month – month when a row appears in a source data (reported month)
  • Agent – name of an agent
  • Agent ID – a unique ID for each agent
  • Day appointed – date when agent starts selling our products
  • Type – remuneration level of an agent

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!

 

Printscreen.PNG

2 ACCEPTED SOLUTIONS

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:

13.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi Lin,

I have tested your formula and... It works like a charm !!!!!!! Thanks a lot for your help 🙂 

 

Kind regards,

Richard

View solution in original post

14 REPLIES 14
v-lili6-msft
Community Support
Community Support

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?

 

3.PNG

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

MONTHAGENTAGENT IDDAY APPOINTEDTYPE# Months on position
2018/02Agent 35722201.02.2018AN1
2018/05Agent 35722201.02.2018AG1
2018/06Agent 35722201.02.2018AG2
2018/08Agent 35722201.02.2018AG4
2018/03Agent 35722201.02.2018AN2
2018/04Agent 35722201.02.2018AN3

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:

13.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Snímka.PNG

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.

17.PNG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

21.PNG

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Snapshot1.PNG

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 

PattemManohar
Community Champion
Community Champion

@sportwatch Thanks for detailed explanation of the scenario. Could you please post the same sample data in Copiable format (just to make life easier)





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

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

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.