Forum Discussion

sovereignauto's avatar
sovereignauto
Helper III
5 years ago
Solved

Monthly running conversion

Good Morning,    So im looking for a way to calculate a rolling monthly conversion:   I have two fields as part of a larger date set [date ref] (inital approach) and then [date sold] (the date of...
Need Help
Tutorial Requests
amitchandak's avatar
amitchandak
Super User
5 years ago

sovereignauto , Please refer to my blog and use how I have done monthly calculation, You can use cumulative revenue

 

Customer Retention Part 3: Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410

 

divide(

calculate(sum(Table[revenue]), filter(all('Customer Age bucket'), [Age] <=max([Age])),

calculate(sum(Table[revenue]), all('Customer Age bucket')))

 

or

 

divide(

calculate(sum(Table[revenue]), filter(allselected('Customer Age bucket'), [Age] <=max([Age])),

calculate(sum(Table[revenue]), allselected('Customer Age bucket')))

  • sovereignauto's avatar
    sovereignauto
    Helper III
    5 years ago

    Thank you for this but for some reason i just cant seem to apply it to my Data. 

    There are records where we may have a customer register but never make a sale.

    I have a Column for the Datediff in my dataset and have not created the secondary Month table as i dont need it to say "month 1" just "1" will be fine.

    so on the matrix I added "converted month" as the Column and "new customer date"  as the rows, and if i add a "count" in i get the right data example below:





    So how do i get that now as a conversion figure so "blanks" i will deal with after as thats potential sales but month "0" should show as a % of the row total and then month "1" should show as a % of the row total where its 0 and 1 added together so in May its 218 / 537 = 40% for month 1 

    im thinking somthing like but i dont know how to get the month-in-matrix part. 

     

    Divide( countx(filter('sale',[sale month] < month-in-matrix), sale[ref])),
    count(sale[ref]))