Forum Discussion

bhmiller89's avatar
bhmiller89
Icon for Helper V rankHelper V
8 years ago

Find Current Value and Previous Value

I have a table that includes Date, MRR, and RAG_Value.

 

The date column is every Friday starting May 4th- Present and will keep running every Friday.

 

 The RAG_Value can range from 0-3. I need to calculate measures for MRR based on current RAG Value and Previous RAG Value

 

 

I tried this but it is incorrect (and returns an error) as I don't need MAX Rag_Value, I need the Rag_Value for the MAX DATE

 

AMBER NormalizedMRR$Current = CALCULATE(SUM(vw_OperationalJobsWithMRR[NormalizedMRR]), MAX(RAGandMRR[RAG_Value])= 2)

 

I do have a date table that isn't currently connected to anything else but is there just in case.

 

Any help is appreciated

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi bhmiller89

    It is not clear for me.

    I don't need MAX Rag_Value, I need the Rag_Value for the MAX DATE 

    AMBER NormalizedMRR$Current = CALCULATE(SUM(vw_OperationalJobsWithMRR[NormalizedMRR]), MAX(RAGandMRR[RAG_Value])= 2)

    From above, it seems you need Rag_Value=2 when date is the MAX DATE, you could try this formula

    Max date=CALCULATE(MAX(RAGandMRR[Date]),ALL(RAGandMRR))

AMBER NormalizedMRR$Current = CALCULATE(SUM(vw_OperationalJobsWithMRR[NormalizedMRR]), FILTER(ALL(RAGandMRR),RAGandMRR[RAG_Value]= 2&&RAGandMRR[Date]=[Max date]))

    Then what do you mean by this sentence

    The RAG_Value can range from 0-3. I need to calculate measures for MRR based on current RAG Value and Previous RAG Value

     

    Best Regards

    Maggie