How to calculate 6 weeks sales prior to starting of fiscal year

  • Riyan

    SSC Enthusiast

    Points: 186

    I want to calculate 6 weeks sales prior to starting of fiscal year.i have date column and a condition

    CALCULATE([SALES],

    'SIMDate'[WEEKSTART]>=FIRSTDATE('SIMDate'[WEEKSTART],'SIMDate'[PRESENTYEAR]="CURRENT")-42

    ,'SIMDate'[WEEKSTART]>=FIRSTDATE('SIMDate'[WEEKSTART],'SIMDate'[PRESENTYEAR]="CURRENT")

    i am trying to return firstdate of the fiscal year and then subtract 42(which means 6 weeks * 7 days= 42).to get the sales for 6 weeks. but i am not getting any results.

  • pietlinden

    SSC Guru

    Points: 62365

    I think it would be something along the lines of

    CALCULATE ( [Total Sales],

    DATESBETWEEN(DATEADD("week",-6,Calendar[Date]),Calendar[Date]) )

  • Riyan

    SSC Enthusiast

    Points: 186

    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?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply