Find Current Value and Previous Value

[bhmiller89]

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

[v-juanli-msft]

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