Forum Discussion

derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
2 years ago
Solved

Need help with getting average monthly activity duration and monthly volume of customer

  Date of Action Customer Volume Transaction Assets 13/01/2020 Customer A               44,499.2              1,148                60 5/03/2020 Customer B                  2,18...
Need Help
Tutorial Requests
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

derekli1700 

pls try this

 

Measure =
var tbl = ADDCOLUMNS( SUMMARIZE('Table','Table'[Customer],"mindate",min('Table'[Date of Action]),"maxdate",max('Table'[Date of Action])),"month", DATEDIFF([mindate],[maxdate],MONTH))
return sumx(tbl,[month])
 
 
Measure 2 = sumx(FILTER('Table','Table'[Customer]="Customer A"),'Table'[Transaction])/ sumx(FILTER('Table','Table'[Customer]="Customer A"),'Table'[Assets])
 
 
pls see the attachment below
 
derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
2 years ago

Hey thats really helpful thanks -

 

For measure 1, my main data has a date heirachy which shows quarter, months, days etc as a dropdown. Does the measure still work for this?

regarding measure 2, is there way for the measure to calculate the overall average customer transaction per assets (rather than one customer)? Thanks

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    for measure 1, not very clear about your request, you can have a try to see if it works.

    there is something wrong in measure 1, pls use averagex instead of sumx

     

    for measure 2, is the similar calculation logic you used for measure 1? 

     

    var tbl = ADDCOLUMNS( SUMMARIZE('Table','Table'[Customer],"mindate",min('Table'[Date of Action]),"maxdate",max('Table'[Date of Action])),"col",sum('Table'[Transaction])/sum('Table'[Assets]))
     
    return average(tbl,[col])