I am looking to build a formula that will look back 2 months, gather the sum of revenue for that month, and multiply it by 12 to get my Projected Sum. So hypothetically, the formula for March would look like:
(Sum of January Revenue) * 12
Once April is here, it would then be
(Sum of February Revenue) * 12
This way if the formula looks back and returns no data, it tells us that specific company has recently been terminated since there was no projected sum reported.
@MFelix I've substitued the Revenue Date for the Date in my calendar table and now I no longer get the error:
Only problem is now it is summing each individual line. I was hoping to get the sum for December (71.28) and multiply that by 12 (855.36) as the answer I was looking for. I don't need totals for older dates, just the latest month. Each month there will be new revenue and revenue dates and I will need to take the total for the new month and multiply it by 12.
Do you want to dispaly the information as you have in your print screen?
Measures are calculated based on context so when you add the full date field, it will make the sum for each individual row and not for the aggregated month. If instead of date you add in Month/year this will give you the sum per month since it will be getting the information for all the dates within the month.
If you have the need to show individual dates do you want t have single day information or cumulative?
Did I answer your question? Mark my post as a solution!