Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to return a previous measures cell value

Hi everyone, I have a table using measures to create calculated values. Ok, nothing new. The table is organized by a "Month Year" column ( I don't have a date field in the table).  I do have a month number column. I want a third column (via a measure) that will replicate the previous month's value. I cant index the table as the Month Year values are not unique. Please see the table below to see what I need. Thank you very much for your help.

 

Month YearMeasure ValuePrevious Measure Value
Aug 2016

25

 
Sep 20164225
Oct 20161742
Nov 20163617
Dec 2016536
Jan 2017625
  • Anonymous's avatar
    Anonymous
    6 years ago

    everyone, I found a soultion that I want to share... here is the Measure with the working solution:

     

    CALCULATE(COUNT('Monthly Data'[File Number]), 'Monthly Data'[Number of days of delinquency] = 0,
    'Monthly Data'[Accounting Status] <> "Charge Off - Bankruptcy" &&
    'Monthly Data'[Accounting Status] <> "Charge Off - Consumer Proposal" &&
    'Monthly Data'[Accounting Status] <> "Charge Off - Fraud" &&
    'Monthly Data'[Accounting Status] <> "Charge Off - Deceased",
    --THE TOPN IS WHERE THE MAGIC HAPPENS!!!
    TOPN(1,FILTER(ALLSELECTED('Monthly Data'),'Monthly Data'[Source Previous Month Calc] < MAX('Monthly Data'[Source Previous Month Calc])),'Monthly Data'[Source Previous Month Calc], DESC)

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    everyone, I found a soultion that I want to share... here is the Measure with the working solution:

     

    CALCULATE(COUNT('Monthly Data'[File Number]), 'Monthly Data'[Number of days of delinquency] = 0,
    'Monthly Data'[Accounting Status] <> "Charge Off - Bankruptcy" &&
    'Monthly Data'[Accounting Status] <> "Charge Off - Consumer Proposal" &&
    'Monthly Data'[Accounting Status] <> "Charge Off - Fraud" &&
    'Monthly Data'[Accounting Status] <> "Charge Off - Deceased",
    --THE TOPN IS WHERE THE MAGIC HAPPENS!!!
    TOPN(1,FILTER(ALLSELECTED('Monthly Data'),'Monthly Data'[Source Previous Month Calc] < MAX('Monthly Data'[Source Previous Month Calc])),'Monthly Data'[Source Previous Month Calc], DESC)