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

DAX DAX Calculations
1

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:

date%20table%201
date table 1.PNG332×613 6.89 KB

Any help would be appreciated!

Thanks,
Nicolas Nouchi

2

Hi Nicolas,

To make this dynamic just add 2 new columns to your calendar table, just create some logic to compare each date in your calendar to NOW in Power Query M or DAX depending on what your more comfortable with.

This can either be an Offset which would return a 0 for the current FiscalWeekOffset/FiscalYearOffset, decreasing negative numbers for past- and increasing positive numbers for future FiscalWeeks/FiscalYears.
Or a Boolean value where the CurrentFiscalWeek/CurrentFiscalYear returns TRUE and all others FALSE, depending on your needs.

Then you can use these new calendar fields as filters in your CALCULATE statement - and your done.

3

Hi Melissa,

Thank you for your help, I ended up realizing I was overthinking this and just developed two calculated columns that identified the current fiscal year and the current fiscal week, utilizing the column that identified the current fiscal year.

Thanks,

Nicolas

4

Hi Nicolas,

Can you post the DAX for your calculated column in the topic so others can benefit from your solution.

Kind regards,
Melissa

5

Hi Nicolas,

Looks like the same issue I had when dealing with Week (Monday to Friday) & Year.

I’ve posted a solution in this thread:

Regards
Hidayat

6

Hi @Melissa,

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

That was able to condense sales revenue to change every week automatically through daily refreshes.

  • Nicolas
7

Thank you for posting your solution Nicolas, others might find it helpful.