cancel
Showing results for
Did you mean:
nnouchi Regular Visitor

## Calculate extended sales by fiscal week with logic to determine fiscal week automatically

Hi everyone,

Would you be able to provide some insight on how I’d be able to create a sum of sales by fiscal week that changes accordingly to the current fiscal week and fiscal year. The current method I’ve been utilizing is just changing it manually.

EX: Week extended sales = CALCULATE(SUM(‘Sales’[Sum of Ext price]),FILTER(‘Fiscal Dates’,‘Fiscal Dates’[FiscalWeekNumber] = 43 && ‘Fiscal Dates’[FiscalYear] = 2019))

My date table has the following column attributes:

Any help would be appreciated!

Thanks,
Nicolas Nouchi

1 ACCEPTED SOLUTION

Accepted Solutions
nnouchi Regular Visitor

## Re: Calculate extended sales by fiscal week with logic to determine fiscal week automatically

Hi there,

Was able to figure it out:

Used two calculated columns to get the fiscal year / fiscal week, then added the fiscal week calculated column into a measure for the week's sales

IsInCurrentFiscalYear = IF(YEAR(NOW())=[FiscalYear],1,0)

IsInCurrentWeek = IF(Dates[IsInCurrentFiscalYear] && WEEKNUM(NOW()) = 'Dates'[FiscalWeekNumber],1,0)

EX: CALCULATE(SUM(Sales[Revenue],FILTER(Dates, Dates[IsInCurrentWeek] = 1))

3 REPLIES 3 Community Support Team

## Re: Calculate extended sales by fiscal week with logic to determine fiscal week automatically

Hi @nnouchi

How do you define the Fiscal Dates/year/weeknum

If i define calendar weeknum from Monday to Sunday, what is the period for your fiscal week?

the current fiscal week and fiscal year?

It mean if today is 2019/10/23, it is in calendar weeknum 43 and calendar year 2019,

then you want to calculate the values where fiscal year 2019 and fiscal weeknum 43,

Right?

nnouchi Regular Visitor

## Re: Calculate extended sales by fiscal week with logic to determine fiscal week automatically

The period for the fiscal week starts on Monday and ends on Sunday. The date calendar starts at 01-01-2017 and ranges all the way to 2022. Yes that is correct, but I'm looking to make this select the current week & year based automatically rather than having to manually change it through a filter.

nnouchi Regular Visitor

## Re: Calculate extended sales by fiscal week with logic to determine fiscal week automatically

Hi there,

Was able to figure it out:

Used two calculated columns to get the fiscal year / fiscal week, then added the fiscal week calculated column into a measure for the week's sales

IsInCurrentFiscalYear = IF(YEAR(NOW())=[FiscalYear],1,0)

IsInCurrentWeek = IF(Dates[IsInCurrentFiscalYear] && WEEKNUM(NOW()) = 'Dates'[FiscalWeekNumber],1,0)

EX: CALCULATE(SUM(Sales[Revenue],FILTER(Dates, Dates[IsInCurrentWeek] = 1))

Announcements #### New Topics Started Badges Coming  