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

[nnouchi]

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

[nnouchi · Accepted solution]

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))

[v-juanli-msft]

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?

read understand test reply

[nnouchi]

Hi @v-juanli-msft ,

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.