Calculation of months with variables

[sportwatch]

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!

[v-lili6-msft · Accepted 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