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 purcha...
Need Help
Show and Tell
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

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

    Thank you for reply. The code above makes sense and I'm trying to do it on my end and it fails due to not being able to use the "This Period" Measure. 

     

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

     

    I tried cleating another measure as "this period" and whatever I do its not avaiable to chose from where I marked it red above.