Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count a number of months after registration

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 da...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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!