I have a YearMonth column as a primary filter and PeriodCategory (Rolling 12, QTD, YTD, MTD) filter as secondary.
Example1: In YearMonth, If I select 201803 then I select Rolling 12 in PeriodCategory, then it should display the Rolling 12 months from 201803 (i.e., 201803,201802, 201801, 201712, 201711 .... 201704). Which means It has CY 2018 and CY-1 2017 as the result. I have amounts coming from a different table as Currency. Currency table has many columns as CY values are in one column as CurrentYear and CY-1 values are in another column as CurrentYear-1 and CY-2 values are in a different column as CurrentYear-3 and so on. Now, I need to calculate the total some from that respective columns. In our example, we selected 201803 and Rolling 12 as filters. Then I need to add amounts for 201803, 201802 and 201801 from CurrentYear column in the currency table and 201712, 201711, 201710,...201704 from the CurrentYear-1 column in the currency table and sum both the values and display a single value.
My data looks like this:
Note: Flag, Year and YearMonth are from Date Table and Rest of the CurrentYear amount columns are from Currency table. For better understanding, I put it like this.
As per the above example by selecting 201803 in YearMonth and Rolling 12 in PeroidCategory, then the Answer should be the sum of CurrentYear Amount value in Currency table where YearMonth in (201801, 201802, 201803) + Sum of CurrentYear-1 Amount value in Currency table where YearMonth in (201704, 201705, 201705, 201706, 201707, 201708, 201709, 201710, 201711 and 201712).
Example 2: If I select the same 201803 in YearMonth and select QTD in PeriodCategory, then there will be only 2018 records (201801,201802 and 201803) which is the current year. So we have to sum only the 3 records and display the answer
All this should be dynamic based on the YearMonth and PeriodCategory selection. So I need this as a measure only but not a calculated column.
Thanks in advance!