Have a look at this link, in Tableau Public, where I mocked up an example for you. Your question has 2 aspects:
1) how to get data from different rows into the current row, and have this calculation moving - this is a typical use case for table calculations, and I created one for you. Table calcs are very powerful, but also have their complexities, such as concepts of adressing and partitioning. If you are not familiar with these, please have a look at some of the great posts of Jonathan Drummey here.
2) How you will be filtering, so that you do not lose data for your first months. Again this calls for use of a table calc filter, that is applied later in the Tableau pipeline. You can use this post of Bora Beran, as a reference for the order of applying different filters. In essence when you filter by a table calculation, Tableau only hides the data in the viz, and this (I believe) is exactly what you need here.
I believe looking at the TWBX will get you started. Best,
You just saved me, the explanation and example through workbook made me completely understand the logic, thank you soooooo much, I appreciate you help.
Though I also tried to figure the logic, I ended with RAWSQLAGG_STR, I made a SQL query with a date range where clause, it gave me the result but the date range was accepting for a particular date. So I have to consolidate whole month's calculation into one, but there was formatting problem. My query was:
"select count(distinct data) from tablename1 1
inner join tablename2 2 on 1.column_no=2.column_no
where 1.date between dateadd(year, -1, %1) and %1
I liked your solution better, and it gave want I actually want.
Thanks once again Vlad.
Glad it helped, Anshul! Take care!
I need one more help, such as if I have two columns like 'Last 12 months Sales' and 'Last 12 months Production', and with respect to selected month and year I need to calculate percentage: Last 12 months Sales/Last 12 months Production. I am trying to calculate it through directly applying it in calculated field, but its showing 1 for all months.
Could you please help me in this...
How about this? I just made the same calculation for profit, and then created a calculation for the gross profit % dividing profit from the last 12 months by sales from the last 12 months. I had to then change the formatting to percentage, as it was showing 0 all over the place. I believe your problem may be just a formatting issue.
You saved me again... thanks a lot Vlad... I definitely owe beer(s) for you.... thanks man..!