Forum Discussion

misiek5510's avatar
misiek5510
Icon for Helper III rankHelper III
4 years ago

Show New Entries Each Month

Hi All,

 

I'm looking for a measure that will show me new entries each month, but will look into whole year first. So if for example new unique ID (customer) will place an order and he never purchased anything this year I need the output to be "new" and if his records are already in the system in current year or last 12 months to be "old".

I tried the below but it gives an error "Function Sum can not work with values of type string". 

new / old = if(isblank(CALCULATE(sum(DATACOMP[User ]),DATESMTD(dateadd(DATACOMP[Date],-12,MONTH)))),"New","Old")
Need Help
Show and Tell

4 Replies

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

    misiek5510 , with slight change in formula, in these you can get

     

    Customer Retention Part 1:
    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
    Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

     

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

     

    Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

     

    This is your last period

    Rolling 12 till last month

     

    Current period

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

     

    rest should be same as blog

     

    • misiek5510's avatar
      misiek5510
      Icon for Helper III rankHelper III
      4 years ago

      The measures above seem to be looking at sales (numbers) about and I'm checking for the username which would be a string so I can not use calculate or sum on it. 

       

      Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

       

      Am I doing something wrong?

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

        misiek5510 , You can use count

         

        Rolling 12 till last month = CALCULATE(Count(Sales[User name]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

         

        If you have checked blog. The basic idea is you have transactions in the current period say a month and do not have transactions before. 

         

         

        This Period = CALCULATE([Sales],Datesmtd('Date'[Date]))

        Last Period = CALCULATE([Sales],DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-12,MONTH))

        New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Last Period]) && not(ISBLANK([This Period])) , 1,BLANK()))

        Retained Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(not(ISBLANK([This Period])) && not(ISBLANK([Last Period])) , 1,BLANK()))

        Lost Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([This Period]) && not(ISBLANK([Last Period])) , 1,BLANK()))

         

         

        now measure [sales] can be replaced with any measure to make sure it gives result when we have transaction