Problems with 12 month rolling sum with distinct values

[Severindj]

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 🙂 

[Mariusz · Accepted solution]

Hi @Severindj 

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.

[Mariusz · Accepted solution]

Hi @Severindj 

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.

[Severindj · Accepted solution]

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]))))