Forum Discussion

mkoontz's avatar
mkoontz
Frequent Visitor
5 years ago
Solved

Get value from previous record based on Period

I have a table that is by Contract, Period (YYYYMM). I have a measure that is grabbing the previous record based on the Period column. This works great when the previous record is in the same year, b...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    mkoontz , Create a rank on YYYYMM in you date table.

    a new column

    Period Rank = RANKX(all('Date'),'Period'[year period],,ASC,Dense)

     

    try measures like these examples 


    This Period = CALCULATE( [measure for column ($)], FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
    Last Period = CALCULATE( [measure for column ($)], FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

mkoontz , Create a rank on YYYYMM in you date table.

a new column

Period Rank = RANKX(all('Date'),'Period'[year period],,ASC,Dense)

 

try measures like these examples 


This Period = CALCULATE( [measure for column ($)], FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE( [measure for column ($)], FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

 

mkoontz's avatar
mkoontz
Frequent Visitor
5 years ago

amitchandak Thank you for the quick reply. Greatly appreciated. Your solution worked great for me.

Thank you again.