Home Forums Data Warehousing PowerPivot How to calculate 6 weeks sales prior to starting of fiscal year RE: How to calculate 6 weeks sales prior to starting of fiscal year

  • In this issue,FISCAL Year is dynamic date which changes every year, usually last sunday of june. For 2015-2016 fiscal year, start date is june 28th,2015. which function i need to use to return first date of fiscal year. i have date column which gives all dates of present fiscal year starting from june 28th,2015. i am trying to pick the first date of fiscal year using FIRST DATE Function then i want to substract 42 days to calculate 6 week sales.

    "6 Week sales",

    CALCULATE ([SALES],

    Filter('SIMDATE',

    'SIMDate'[FULLDATE]>=FIRSTDATE('SIMDate'[FULLDATE]-42

    && 'SIMDate'[FULLDATE]<=FIRSTDATE('SIMDate'[FULLDATE]))

    Does it works?