Forum Discussion

DANinja's avatar
DANinja
Regular Visitor
3 years ago
Solved

Time intelligence problems

Hi,

New to this forum but been scrolling through questions and learned a lot but haven't found a solution to my problems.

 

I need a 12 Month sum of sales beginning LAST month...the solution I've used is

 

OppCreated R12 = CALCULATE(sum(FactOpportunity[Amount]),FactOpportunity[OppSourceSystemName]="CRMSE",FactOpportunity[Intercompany]<> "Intercompany",DIMSalesprocess[EU Sales Process]<>"Lost",DIMSalesType[Nordic Salestype]<>"Migration",DIMSalesType[Nordic Salestype]<>"Renewal",DIMOrganization[Category]="KAM",'FactOpportunity'[CustomerOrgNr]<> "5561234567",DATESINPERIOD(DIMDateCreated[Date],EOMONTH(TODAY(),0),-12,MONTH))

 

Inn the end that means that  you're comparing 11 months up until the very end of the current month and I want to compare 12 full months ie I need to calculate from the last full month and 12 months backwards.

 

My other problem is similar where I need to find out how many renewal contracts I got for the next month. 

 

Renewals  = Calculate(sum(FactOpportunity[Amount]),'DIMSalesType'[Nordic Salestype]="Renewal",FactOpportunity[Stage]<> "Felaktig",FactOpportunity[Stage]<> "Avtal förlorat",FactOpportunity[Intercompany] <> "Intercompany",DIMOrganization[Category] = "KAM",DIMCountry[Country] = "Sweden",DATESINPERIOD(DIMDateClosed[Date],EOMONTH(TODAY(),0),1,MONTH))
The formula above also gives me the last day of this month renewal as well which wasn't intended...I just need for ex December renewals without the 30th of November.

 

Been trying to google a solution but now I have to ask the expertise in this forum.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi DANinja ,

    I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_ Binbin Yu

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi DANinja ,

    I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_ Binbin Yu

    • DANinja's avatar
      DANinja
      Regular Visitor
      3 years ago

      Hi 

      You can remove my post since I managed to solve it myself after some additional googling.

      Rgs,

      J