cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nnouchi Regular Visitor
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
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))
 

View solution in original post

3 REPLIES 3
Community Support Team
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?

 

read understand test reply

 

nnouchi Regular Visitor
Regular Visitor

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

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.

 

 

nnouchi Regular Visitor
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))
 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)