Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Hi,
Share the download link of the MS Excel workbook.
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.
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.
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.
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!
You are welcome.
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 @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
Hi Shaurya - I should have specified, I am trying to generate the ending customer column from the other 4.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |