no calendar table
1 Topic
Show last N months based on FiscalYearMonth slicer selection without calendar/date table
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.Solved761Views0likes3Comments