Forum Discussion
Calculated Column LTM (Last 12 Months)
Hello - I am trying to create a calculated column (I want to avoid using a measure for this) that will show the LTM if the row meets certain criteria. I have a small example below (attached as well.) I need column E to be the LTM only if the platform is Android and the Type is Annual; otherwise give me customers. Here is a screen shot of what it would look like in Excel.
Thank you
Hi,
Write this calculated column formula
Ending customers = if(and(Data[Platform]="Android",Data[Type]="Annual"),CALCULATE(SUM(Data[Customers]),FILTER(Data,Data[Platform]=EARLIER(Data[Platform])&&Data[Type]=EARLIER(Data[Type])&&Data[AsofDate]>=EDATE(EARLIER(Data[AsofDate]),-12)&&Data[AsofDate]<=EARLIER(Data[AsofDate]))),Data[Customers])Hope this helps.
11 Replies
Hi Dcurtis7640,
Use:
LTM = IF('Table'[Platform]="Android" && 'Table'[Type]="Annual", 'Table'[Ending Customer], 'Table'[Customer])Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous ForecastsHi Shaurya - I should have specified, I am trying to generate the ending customer column from the other 4.
Hi,
Share the download link of the MS Excel workbook.
Here is a link to the Google Sheet
https://docs.google.com/spreadsheets/d/1sceFOT4IiNqOjguOjCuJNbAOG4-ksKD2/edit#gid=1086682522
Hi,
Write this calculated column formula
Ending customers = if(and(Data[Platform]="Android",Data[Type]="Annual"),CALCULATE(SUM(Data[Customers]),FILTER(Data,Data[Platform]=EARLIER(Data[Platform])&&Data[Type]=EARLIER(Data[Type])&&Data[AsofDate]>=EDATE(EARLIER(Data[AsofDate]),-12)&&Data[AsofDate]<=EARLIER(Data[AsofDate]))),Data[Customers])Hope this helps.
Hi Ashish_Mathur, thank you for the reply! I am still running into some issues but I think I am close. Would it be possible to send you a bigger subset of my table?
You are welcome. Be very clear about the issue you are facing and also show the expected result.
