My requirement
Ex-1 When the user select a value say 202501 from slicer "FiscalYearPeriodCd" which is a text data type. The table below should display last 13 months . Ie from 202401 to 202501. ---Last 13 months
Ex-2 When the user selects "202411" , the table below should display SalesUSD from 202311 to 202411 .---Last 13 months.
.Unfortunately i have a calendar table but the calendar table and fact table are related using someother key and not a datekey as per the business requirement and hence i cannot change it.Also in my date table , i have the date repeated twice.One is for US and another for Non US due to fiscal calculation.Hence i cannot use datesinperiod , dateadd or other date related functions.Hence the only option is to use FiscalYearPeriodCd.
Raw data below
| SalesUSD | FiscalYearPeriodCd |
| 534132759.5 | 202201 |
| 1557801979 | 202202 |
| 695221259.1 | 202203 |
| 813135024.7 | 202204 |
| 832097591.8 | 202205 |
| 755491149.2 | 202206 |
| 848978369.6 | 202207 |
| 831567295.5 | 202208 |
| 907314200.6 | 202209 |
| 822103615.4 | 202210 |
| 674213875.8 | 202211 |
| -2879970782 | 202212 |
| 859510773.8 | 202301 |
| 908174754.8 | 202302 |
| 856401757.6 | 202303 |
| 864263784 | 202304 |
| 880142006.9 | 202305 |
| 861088774.2 | 202306 |
| -2353615032 | 202307 |
| 968984985.4 | 202308 |
| -1857687494 | 202309 |
| 868514132.3 | 202310 |
| -66961618.2 | 202311 |
| 1317854317 | 202312 |
| 1060522633 | 202401 |
| 1011795775 | 202402 |
| 829441979.7 | 202403 |
| 1100402094 | 202404 |
| 1064195484 | 202405 |
| 1138271467 | 202406 |
| 1898901.5 | 202407 |
| 754629775 | 202408 |
| 130722055.9 | 202409 |
| 1437834704 | 202410 |
| 912161100.4 | 202411 |
| 167628959.9 | 202412 |
| 486353211.6 | 202501 |
| 98759203.23 | 202502 |
Expected output for Ex 1 slicerselection = 202501
| MgmtRateUSD | FiscalYearPeriodCd |
| 1060522633 | 202401 |
| 1011795775 | 202402 |
| 829441979.7 | 202403 |
| 1100402094 | 202404 |
| 1064195484 | 202405 |
| 1138271467 | 202406 |
| 1898901.5 | 202407 |
| 754629775 | 202408 |
| 130722055.9 | 202409 |
| 1437834704 | 202410 |
| 912161100.4 | 202411 |
| 167628959.9 | 202412 |
| 486353211.6 | 202501 |
expected output for ex 2 - slicerselection = 202411
| MgmtRateUSD | FiscalYearPeriodCd |
| -66961618.2 | 202311 |
| 1317854317 | 202312 |
| 1060522633 | 202401 |
| 1011795775 | 202402 |
| 829441979.7 | 202403 |
| 1100402094 | 202404 |
| 1064195484 | 202405 |
| 1138271467 | 202406 |
| 1898901.5 | 202407 |
| 754629775 | 202408 |
| 130722055.9 | 202409 |
| 1437834704 | 202410 |
| 912161100.4 | 202411 |
Thank you so much in advance.
