Forum Discussion
Dynamic sum based on filters and values
Hi,
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 CurrentYear-1 column in the currency table and sum both the values and display a single value.
All this should be dynamic based on the YearMonth and PeriodCategory selection. So I need this as a measure but not a calculated column.
My Data looks like this.
| Year | YearMonth | CurrentYear | CurrentYear-1 | CurrentYear-2 | CurrentYear-3 | CurrentYear-4 |
| 2018 | 201801 | ($2,783,800.00) | ($2,806,989.25) | ($2,830,108.04) | ($1,178,861.94) | ($1,159,263.98) |
| 2018 | 201802 | $1,836,500.00 | $1,851,798.17 | $1,867,049.86 | $777,706.71 | $764,777.75 |
| 2018 | 201803 | $34,468,100.00 | $34,755,221.67 | $35,041,470.95 | $14,596,282.50 | $14,353,626.95 |
| 2017 | 201704 | $2,208,000.00 | $2,226,392.79 | $2,244,729.70 | $935,026.64 | $919,482.31 |
| 2017 | 201705 | $12,285,100.00 | $12,387,435.74 | $12,489,460.54 | $5,202,398.46 | $5,115,911.31 |
| 2017 | 201706 | $24,497,800.00 | $24,701,868.38 | $24,905,316.72 | $10,374,137.52 | $10,201,672.92 |
| 2017 | 201707 | $4,713,500.00 | $4,752,763.78 | $4,791,908.27 | $1,996,036.26 | $1,962,853.21 |
| 2017 | 201708 | $21,226,700.00 | $21,403,519.89 | $21,579,802.53 | $8,988,917.57 | $8,839,481.53 |
| 2017 | 201709 | $34,141,600.00 | $34,426,001.90 | $34,709,539.68 | $14,458,018.83 | $14,217,661.84 |
| 2017 | 201710 | $23,983,600.00 | $24,183,385.06 | $24,382,563.09 | $10,156,388.11 | $9,987,543.48 |
| 2017 | 201711 | $15,579,000.00 | $15,708,774.15 | $15,838,154.00 | $6,597,273.57 | $6,487,597.35 |
| 2017 | 201712 | $97,794,700.00 | $98,609,336.66 | $99,421,498.12 | $41,413,337.80 | $40,724,862.75 |
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 + CurrentYear-1 = $271,920,278.35
| YearMonth | CurrentYear |
| 201801 | ($2,783,800.00) |
| 201802 | $1,836,500.00 |
| 201803 | $34,468,100.00 |
+
| YearMonth | CurrentYear-1 |
| 201704 | $2,226,392.79 |
| 201705 | $12,387,435.74 |
| 201706 | $24,701,868.38 |
| 201707 | $4,752,763.78 |
| 201708 | $21,403,519.89 |
| 201709 | $34,426,001.90 |
| 201710 | $24,183,385.06 |
| 201711 | $15,708,774.15 |
| 201712 | $98,609,336.66 |
Can someone please help me with this?
Thanks
1 Reply
v-jiascu-msftMicrosoft Employee
Hi Anonymous,
1. Does your original data look like that?
2. How does the table 'PeriodCategory' connect with other tables?
Can you share a dummy sample file, the pbix file? You can upload it to the cloud drive like OneDrive, Dropbox then share the download link here.
Best Regards,
Dale