Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

12 month rolling calculation

Hi,

I am trying to get 12 month values from the below table, there will a same CRM ID 4 order loaded but its different date, when i am trying the below Measure to filter 12 month values i am getting same values repeating, but i need result like 

1, only based on CRM id what is the current 12 month Achievement .

2, Based on the CRM id SBU wise what is the current 12 month Achievement.

 

 

 

RAG 12 =
 VAR CDay = MAX(RAG[Dated])
 VAR PDay = DATE(YEAR(CDay)-1,MONTH(CDay),DAY(CDay))
 VAR Result =
 CALCULATE(
    SUM(RAG[Full Achv]),
 FILTER('RAG','RAG'[Dated]>=PDay && 'RAG'[Dated]<=CDay)
 )
 RETURN
 Result

 

 

Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Anonymous , Try updating your measue as 

    RAG_12_CRM =
    VAR CDay = MAX(RAG[Dated])
    VAR PDay = DATE(YEAR(CDay)-1, MONTH(CDay), DAY(CDay))
    RETURN
    CALCULATE(
        SUM(RAG[Full Achv]),
        FILTER(
            RAG,
            RAG[Dated] >= PDay && RAG[Dated] <= CDay
        )
    )
     
     
    And 
     
    RAG_12_CRM_SBU =
    VAR CDay = MAX(RAG[Dated])
    VAR PDay = DATE(YEAR(CDay)-1, MONTH(CDay), DAY(CDay))
    RETURN
    CALCULATE(
        SUM(RAG[Full Achv]),
        FILTER(
            RAG,
            RAG[Dated] >= PDay && RAG[Dated] <= CDay
        ),
        ALLEXCEPT(RAG, RAG[CRM ID], RAG[SBU])
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Bhanu,

      Thank you for Quick replying, 

      Still i am getting same issue,(Values is repeating).

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you so much Bhanu, Its working fine now--😍