Previous Week Value i.e., (max(week)-1) for more than 1 year data

[Anonymous]

Hi All,

Provided my original dataset and Prev Week value

The problem is when we are going from 2019 to 2020 i am facing issue. When more than 1 year of data is involved then i am not getting 2020-1 i.e., first week of 2020 value for Previous week.

Provided the measure which i am using for 2nd table shohwn in above screenshot

prev weeknum =

IF(

NOT ISEMPTY( 'Calculated Table' ),

VAR __maxWeek = MAX('Calculated Table'[C_WeekNum])

RETURN

CALCULATE(

SUM('Calculated Table'[C_Sales]),

'Calculated Table'[C_WeekNum] = __maxWeek-1,

ALLEXCEPT( 'Calculated Table','Calculated Table'[Time_Dimension])

))

How to add year scenario here? Might be something like when weeknum = 1 then year = year - 1 and take max(week) from prev year to compare 1 st week of next years data.

I like to just alter the above query.

Thanks in advance

[amitchandak]

If you have only weeks in your dimensions. Then refer to this solution

https://community.powerbi.com/t5/Desktop/Doubt-in-calculating-sum-of-last-6-weeks-sum/td-p/913870/pa...

In case you have dates try

This week sales = 
var _date = date(year(today()),month(today()),day(today()))
var _Week_ED = _date + (-1*WEEKDAY(_date)+1)
var _Week_SD= _date +(7-1*WEEKDAY(_date))

Return 
CALCULATE(sum(Sales[Sales Amount]),all('Date'),Sales[Sales Date]>=_Week_SD && Sales[Sales Date]<=_Week_ED)


Last week sales = 
var _date = date(year(today()),month(today()),day(today()))
var _Week_ED = _date +( (-1*WEEKDAY(_date)+1)-7)
var _Week_SD= _date +((7-1*WEEKDAY(_date))-7)

Return 
CALCULATE(sum(Sales[Sales Amount]),all('Date'),Sales[Sales Date]>=_Week_SD && Sales[Sales Date]<=_Week_ED)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi @amitchandak 

I couldn't able to open the pbix file which was shared in the below path.

https://community.powerbi.com/t5/Desktop/Doubt-in-calculating-sum-of-last-6-weeks-sum/td-p/913870/pa...

It is showing access denied for me (Some issue from my side to the access)

Thanks

[amitchandak]

Try this link

[Anonymous]

Unfortunately still couldn't able to open that link.

I hope we need to create a max year variable and whenever weeknum<2 then Max of year - 1 like something.

IF(

NOT ISEMPTY( 'Calculated Table' ),

VAR __maxWeek = MAX('Calculated Table'[C_WeekNum])

RETURN

CALCULATE(

SUM('Calculated Table'[C_Sales]),

'Calculated Table'[C_WeekNum] = __maxWeek-1,

ALLEXCEPT( 'Calculated Table','Calculated Table'[Time_Dimension])

))

[amitchandak]

if you have week dimension table and it has  weekno is like YYYYWW
Then

RankedWeek = RANKX(Week,Week[WeekNo],,ASC)


Sales this Weeks = CALCULATE(SUM('Table'[Sales]), FILTER(ALL(Week), Week[RankedWeek] = MAX(Week[RankedWeek]) ))

Sales Last Weeks = CALCULATE(SUM('Table'[Sales]), FILTER(ALL(Week), Week[RankedWeek] = (MAX(Week[RankedWeek])-1) ))

[Anonymous]

Hi @amitchandak ,

The below code is working fine for me but i dont want to give 52 as static. Can we make this as dynamic?

prev week try1 =

IF(

NOT ISEMPTY( 'Calculated Table' ),

VAR _maxWeek = MAX('Calculated Table'[C_WeekNum])

Var _maxYear = MAX('Calculated Table'[C_Year])

RETURN

IF(_maxWeek<2,

CALCULATE(

SUM('Calculated Table'[C_Sales]),

'Calculated Table'[C_Year] = _maxYear-1,'Calculated Table'[C_WeekNum] = 52,

ALLEXCEPT( 'Calculated Table','Calculated Table'[Time_Dimension])

),

CALCULATE(

SUM('Calculated Table'[C_Sales]),

'Calculated Table'[C_Year] = _maxYear,'Calculated Table'[C_WeekNum] = _maxWeek-1,

ALLEXCEPT( 'Calculated Table','Calculated Table'[Time_Dimension])

)

))

Thanks

[amitchandak]

Try like . Do necessary modification

Prev Week =
var _date = date([C_Year]-1,12,31)
var _maxPweek = weekno(_date)
VAR _maxWeek1 = MAXX('Calculated Table','Calculated Table'[C_WeekNum])
VAR _maxWeek = if([_maxWeek1]=1 ,_maxPweek, _maxWeek1-1)
var _cYear =MAXX('Calculated Table','Calculated Table'[C_Year])
Var _maxYear = if([_maxWeek1]=1,_cYear-1,_cYear)
return 
CALCULATE(
SUM('Calculated Table'[C_Sales]),
'Calculated Table'[C_Year] = _maxYear,'Calculated Table'[C_WeekNum] = _maxWeek,
all( 'Calculated Table')
)

[Anonymous]

Hi @amitchandak ,

It is throwing an error

Thanks