Count a number of months after registration

[Anonymous]

Greetings,

I have a task that seems simple, but I can't address it.

There are 2 linked tables

1. Dates (with columns Date, Month, Year, Month+Year)

2. Clients (Client ID and Registration date)

I need to calculate the number of months after registration for each client.

After that, I need to get a measure that gives me an average of the above calculation per month.

Any help is highly appreciated!

[amitchandak]

In case you want a column

new column in client = datediff(client[date],today(),MONTH)

In case you want measure dynamic to change based on selection
new Measure = calculate(averagex(summarize(client,client[Id], "_reg_date",min(client[date]),"_max_td",max(date[date])),datediff(_reg_date,_max_td,MONTH)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi @amitchandak ,

Thank you for your answer!

I applied this measure formula and it partially works.

Unfortunately, it shows "0" in a month when registration was done and Blank in other cells.

External link to the picture 

Could you advise how to fix it?

Thanks,

Oleg

[amitchandak]

Missed [ and ]

new Measure = calculate(averagex(summarize(client,client[Id], "_reg_date",min(client[date]),"_max_td",max(date[date])),datediff([_reg_date],[_max_td],MONTH)))

[Anonymous]

Hi @amitchandak,

Sorry, I don't fully understand what was missed.

I checked your last formula with a previous and the one I use, all of them are the same.

Could you specify in details what I need to change?

Thank you,

Oleg

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measures to meet your requirement:

Measure = AVERAGEX('Clients',DATEDIFF(CALCULATE(MIN('Clients'[Registration date])),CALCULATE(MIN('Dates'[Date])),MONTH))

Best regards,

[Anonymous]

Hi @v-lid-msft ,

Thank you for your suggestion!

I don't know why I got "0" again.

Those "0" are on the month when registration was done, so it identifies it correctly.

There are no filters, all columns data types are correct.

Here is a screenshot(I don't know why I can't add an image here):

https://drive.google.com/open?id=1drWIZ-c-ZBiyQeIUdVvMbDkryawC0ND0

[amitchandak]

In averagex try the first parameter as averagex(values(client[clientID])

[Anonymous]

Sorry, it did not help. 😞

https://drive.google.com/open?id=1odHd5gv2RkBBnvTFukZFBAm6T9uyq3Kx

[v-lid-msft]

Hi @Anonymous ,

We can try to use the following measure to met your requirement if two tables have 1:M relationship:

Measure = 
VAR d =
    MIN ( 'Dates'[Date] )
RETURN
    AVERAGEX (
        FILTERS('Clients'[Client ID]),
        var i = [Client ID] return
        DATEDIFF (
            CALCULATE (
                MIN ( 'Clients'[Registration date] ),
                FILTER (
                    ALLSELECTED(  'Clients' ),
                    'Clients'[Client ID] = i
                )
            ),
            d,
            MONTH
        )
    )
    

Best regards,

[Anonymous]

Hi @v-lid-msft ,

Yes, this measure works in the same way as shown in your image, thank you!

The only issue that is left that I need to avoid negative numbers in order to show a true average per month.

Filter by measure value doesn't help.

Could you help me with this?

Here is my picture

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Measure = 
VAR d =
    MIN ( 'Dates'[Date] )
RETURN
    AVERAGEX (
        FILTERS('Clients'[Client ID]),
        var i = [Client ID] 
        var r =
        DATEDIFF (
            CALCULATE (
                MIN ( 'Clients'[Registration date] ),
                FILTER (
                    ALLSELECTED(  'Clients' ),
                    'Clients'[Client ID] = i
                )
            ),
            d,
            MONTH
        )
        return IF(r>0,r,BLANK())
    )
    

Best regards,

[Anonymous]

Hi @v-lid-msft,

Great! It is perfect.

The only thing I changed is

 return IF(r>=0,r,BLANK())

Otherwise, the first month is lost.

A picture of result

Many many thanks!