Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Problems with 12 month rolling sum with distinct values

Dear communnity

I am trying to trying to calculate a rolling 12 month sum with distinct values from different reviewers 

I am however stuck because my formula returns the value of the given date, and not measuring the 12 m sum. 

Our formula looks like this 

LMT2 = SUMX(distinct(Response[socialsuite__Reviewer__c]);
CALCULATE(CALCULATE(SUM(Response[Response: Response]);
FILTER(all(Response[Response.Survey: Due Date]);
    Response[Response.Survey: Due Date]>MAX(Response[Response.Survey: Due Date])-365 &&
    Response[Response.Survey: Due Date]<=MAX(Response[Response.Survey: Due Date])))))

We are using data from salesforce 
Response[socialsuite__Reviewer__c] refers to each reviewer. 
Response[Response: Response]) refers to the numeric values of each response to a survey
Response[Response.Survey: Due Date] refers to the date that should be used for the 12 month rolling sum. 

Hope someone can help :) 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

     

    Try somthing like below.

     

    Sales Rolling 12 months = 
CALCULATE(
    [Sales],
    DATESINPERIOD(
        'Calendar'[Date],
        MIN('Calendar'[Date]) -1,  
        -12,
        MONTH
    )
)

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks for helping, but we found out ourselves. It needs to include the sumx of distinct values, which is a little more tricky. Only works with calculations though. 

    LMT3 = SUMX(distinct(Response[socialsuite__Reviewer__c]);
CALCULATE(SUM(Response[Response: Response]);
FILTER(ALL(Response[Response.Survey: Due Date]);
    Response[Response.Survey: Due Date]>MAX(Response[Response.Survey: Due Date])-365 &&
    Response[Response.Survey: Due Date]<=MAX(Response[Response.Survey: Due Date]))))

2 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

     

    Try somthing like below.

     

    Sales Rolling 12 months = 
CALCULATE(
    [Sales],
    DATESINPERIOD(
        'Calendar'[Date],
        MIN('Calendar'[Date]) -1,  
        -12,
        MONTH
    )
)

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for helping, but we found out ourselves. It needs to include the sumx of distinct values, which is a little more tricky. Only works with calculations though. 

      LMT3 = SUMX(distinct(Response[socialsuite__Reviewer__c]);
CALCULATE(SUM(Response[Response: Response]);
FILTER(ALL(Response[Response.Survey: Due Date]);
    Response[Response.Survey: Due Date]>MAX(Response[Response.Survey: Due Date])-365 &&
    Response[Response.Survey: Due Date]<=MAX(Response[Response.Survey: Due Date]))))