How to calculate the prior month

[Diere29]

I am trying to calculate the prior month for a dataset where they want to view all months consistently at once, the table below is what I am aiming to achieve. The current Formula I have brings back the values as expected expected for all months except December, how do I get it to bring back December for 2023 in the table below in a dynamic way. 

Table example:  

Month & Year	Valued Cost	Valued Cost Prior Month
Jan-24	37,325,215
Feb-24	35,210,811	37,325,215
Mar-24	36,598,001	35,210,811
Apr-24	21,395,509	36,598,001
May-24	39,601,608	21,395,509

Valued Cost Prior Month =  
Above(Sum({<Metric_Days={'Total Valued Cost'}>} [UK_IP_INVFIFO.VALUE]), 1)

[ramazanerduran]

Hi,

Below script will probably solve the issue;

SUM({<MONTH_FIELD={'$(=DATE(ADDMONTHS(TODAY(), -1), 'MM-YYYY'))'}>} MEASURE_FIELD)

NOTE:

`MM-YYYY` section and other fields may need to be change.

Best regards,

Ramazan

[Qrishna]

if you are showing [Month & Year] from Jan-24, try something like below:

if([Month & Year]= Date(Min(TOTAL if([Month & Year]<>'Dec-23',[Month & Year])), 'MMM-YY')
, only({$<[Month & Year]={'$(=Date(Min(TOTAL [Month & Year]), 'MMM-YY'))'}>}[Valued Cost])
, Above([Valued Cost]))

//if not Min try Date(AddMonths(Date#([Month & Year], 'MMM-YY'), -1), 'MMM-YY') for prev month

if you are showing all months try Above([Valued Cost])

where [Valued Cost] = your expression

[Diere29 · Accepted solution]

The above function worked once I changed the date field to one that exists on the main fact table.

[Qrishna]

Please close the thread marking the appropriate comment as answer.

[Diere29]

Have done, thanks for helping and reviewing.