# how to calculate 6 weeks sales prior to starting of fiscal year.i

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

• 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 Wednesday, April 6, 2016 9:51 PM
• Marked as answer by Saturday, April 16, 2016 6:41 AM
Wednesday, April 6, 2016 6:11 PM