Hi
I create the custom week in my date table(our strange financial year) so I want to calculate the Last week revenue based on custom week table but it doesn’t work
below is the formula I used:
LW Revenue =
CALCULATE(
[Voice],FILTER(‘Date’,‘Date’[MTNWeekNo] = MAX(‘Date’[MTNWeekNo])
&& ‘Date’[MTNWeekNo] <= MAX(‘Date’[MTNWeekNo])
))
Daily_Report.pbix (475.9 KB)
Hi
Try This:
PREVIOUS Week Revenue =
VAR
CurrentWeek = SELECTEDVALUE( 'Date'[MTNWeekNo] )
VAR
CurrentYear = SELECTEDVALUE( 'Date'[Year] )
VAR
MaxWeekNum = CALCULATE( MAX( 'Date'[MTNWeekNo]); ALL( 'Date' ) )
RETURN
IF( HASONEVALUE('Date'[MTNWeekNo] );
SUMX(
FILTER( ALL( 'Date' );
IF(CurrentWeek= 1;
'Date'[MTNWeekNo] = MaxWeekNum && 'Date'[Year] = CurrentYear - 1;
'Date'[MTNWeekNo] = CurrentWeek - 1 && 'Date'[Year] = CurrentYear )); [Total Revenue] );
BLANK() )
Paul
1 Like
Thank you so much Paul
Great.
Recommend to watch the video’s on the channel on time intelligence and time comparison. This is where I picked this up. Oddly the standard DAX formulas do not include a week option. I noticed you have created a calendar in Excel which is fine, are you aware you can download a script Sam made that automatically creates an extensive dates table? I have created an empty PBI model with the script and I add my data to this template, so I consistently use the same calendar. One thing to check is weeknumber 1 in every year, depending on country settings you may find your week 1 to start on another day.
1 Like