Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Last 3 months data for user activities

Hi ,im trying to create a measure that calculate and visualize the number of activities(eg: creating policies) performed by user in last 3 months( month by month) and also in a same or another visual to show total count of last 3 months when username is selected.

 

DateActivityusername
1/5/2022policy creationDD
21/5/2022policy creationAA
29/5/2022policy creationAA
6/6/2022policy creationDD
15/06/2022policy creationDD
28/6/2022policy creationBB
1/7/2022policy creationCC
10/7/2022policy creationAA
20/07/2022policy creationJJ

 

Output:

 

DateUsername count of activity
May-2022DD1
May-2022AA2
Jun-2022DD2
Jun-2022BB1
Jul-2022CC1
Jul-2022AA1
Jul-2022JJ1
 Total9
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Is your data coming from the same table? In my example data the measure returns the correct result.

     

    Measure = 
VAR currentmonth =
    EOMONTH ( MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ), 0 )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Activity] ),
        FILTER (
            'Table',
            'Table'[Date] > EDATE ( currentmonth, -3 )
                && 'Table'[Date] <= currentmonth
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Create a measure like this and use in visual

     

    Last 3 Months Today =
    var _min = eomonth(today(),-3)+1
    var _max = eomonth(today(),0)
    return

    CALCULATE(countrows(Table) , FILTER('Table','Table'[Date] >=_min && 'Table'[Date] <= _max))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak  - This calculates and provides the total count but not the count of activites of users performed in the specific month (third column).

    any help is appreciated .

     

    p.s. basically on the x axis the user names to be displayed and on yaxis the count of activities that they have done in the individual months 

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Is your data coming from the same table? In my example data the measure returns the correct result.

     

    Measure = 
VAR currentmonth =
    EOMONTH ( MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ), 0 )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Activity] ),
        FILTER (
            'Table',
            'Table'[Date] > EDATE ( currentmonth, -3 )
                && 'Table'[Date] <= currentmonth
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.


    Best Regards,
    Winniz