Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sum Sales for Rolling Companies Issue

Hi all,

 

In my company you can buy "online" and via "api". 

 

For each month, we need to know how many companies have ordered via API within the last 18 months (rolling). So I have created this measure, which works fine:

 

rolling 18 api = CALCULATE(DISTINCTCOUNT(Company_APISales[CompanyId]),

DATESINPERIOD(DimCalendar[Date],ENDOFMONTH(Company_APISales[Order Start Date.Date]),-18,month))
 
This shows for each month how many companies have ordered through API within the last 18 months.
 
Then I have another table called "FullSales" which have data for all Sales (Online and API).
 
I need to sum the FullSales for API customers in the same way as Rolling 18.
 
Example:
 
If a company order through API on Jan2019 and not after this, then they count in my "Rolling 18" company count until June2020.
 
Then I need to sum the Revenue of this company also from Jan2019-June2020 per month for the "FullSales". (From July2020 and forward, it should not be included anymore, but still remain included in Jan19-June20)
 
So basically, the same months a company count in my "Rolling 18 months", the same months I would need to sum the "FullSales".

 

How would I do this?

Thanks!

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    Not really sure if I understand the calculation you need, I'm lost when you refer:

     

    that the same months a company count in my "Rolling 18 months", the same months I would need to sum the "FullSales"

     

    Do you want to calculate the values for the FullSales of the month that the company purchase trough API so basically with you example you will pick up only 2019 for the full sales instead of the 18 months?

     

    Can you share a sample data and expected results please.

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi , Anonymous 

    Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

     

    Best Regards,
    Community Support Team _ Eason