locked
how to calculate 6 weeks sales prior to starting of fiscal year.i RRS feed

  • Question

  • 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?

    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.


    Wednesday, April 6, 2016 12:53 AM

Answers

  • Hi Gawtam, it looks like SIMDATE is your date table, and when you are able to get the first day with FIRSTDATE(SIMDATE[FULLDATE]), it means you don't have any dates prior to the first day of your fiscal year. In this case, subtracting 42 will return a date but it is not available in your date table, so the calculation will not return much (maybe only this first day).

    The solution is to have a full date table covering all dates present in your model, or more. You should have a measure that returns the first day of the fiscal year, say [FirstDay].

    To calculate sales for the last 6 weeks, you can use the DATESINPERIOD function:

    CALCULATE([Sales]; DATESINPERIOD(SIMDate[FULLDATE];[FirstDay];-42;day))

    Note that the first day of the year is included in the 42 days, you may want to subtract 1 from [FirstDay] to avoid this.

    • Proposed as answer by Michael Amadi Wednesday, April 6, 2016 9:51 PM
    • Marked as answer by Charlie Liao Saturday, April 16, 2016 6:41 AM
    Wednesday, April 6, 2016 6:11 PM
    Answerer