Calculated Column LTM (Last 12 Months)

[Dcurtis7640]

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

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel workbook.

[Dcurtis7640]

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?

[Ashish_Mathur]

You are welcome.  Be very clear about the issue you are facing and also show the expected result.

[Dcurtis7640]

Here is the code that I used:

Ending customers = if(and('Archive vw_Plus_MonthyPaidSubs_Counts'[subpaymenthandler]="Android",'Archive vw_Plus_MonthyPaidSubs_Counts'[subtype2]="Year"),CALCULATE(SUM('Archive vw_Plus_MonthyPaidSubs_Counts'[Customers]),FILTER('Archive vw_Plus_MonthyPaidSubs_Counts','Archive vw_Plus_MonthyPaidSubs_Counts'[subpaymenthandler]=EARLIER('Archive vw_Plus_MonthyPaidSubs_Counts'[subpaymenthandler])&&'Archive vw_Plus_MonthyPaidSubs_Counts'[subtype2]=EARLIER('Archive vw_Plus_MonthyPaidSubs_Counts'[subtype2])&&'Archive vw_Plus_MonthyPaidSubs_Counts'[AsofDate]>=EDATE(EARLIER('Archive vw_Plus_MonthyPaidSubs_Counts'[AsofDate]),-2)&&'Archive vw_Plus_MonthyPaidSubs_Counts'[AsofDate]<=EARLIER('Archive vw_Plus_MonthyPaidSubs_Counts'[AsofDate]))),'Archive vw_Plus_MonthyPaidSubs_Counts'[Customers])

But the running totals don't seem to add up.  I have two tabs to the Google Sheet - the raw data is a copy of the PBI table after I added the code you sent earlier and the pivot tab summaerizes the results - column D on the pivot tab shows my expected result.

[Ashish_Mathur]

Hi,

Your formula is diffeent from the one that i had shared.  My formulas got you the exact result you had shown in your original post.  Now that you have a lot more conditions, please state them clearly on a small dataset and show the expected result.

[Dcurtis7640]

Hello - thank you again! I realized I had to add the condition for the country to be equal when using my larger data set.  Once I added that and changed the EDATE function to go back 11 months instead of 12, I was able to get my desired solution.  Thank you again for all of your help!

[Ashish_Mathur]

You are welcome.

[Dcurtis7640]

Here is a link to the Google Sheet

https://docs.google.com/spreadsheets/d/1sceFOT4IiNqOjguOjCuJNbAOG4-ksKD2/edit#gid=1086682522 

[Ashish_Mathur · Accepted solution]

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.

[Shaurya]

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 Forecasts

[Dcurtis7640]

Hi Shaurya - I should have specified, I am trying to generate the ending customer column from the other 4.