Previous week calculation without relative date

[Anonymous]

Hello Everyone,

I am calculation previous week percentage. I am using currweekoffset(Which shows how week is different from current week) value.

But when I am using it it showing blanks.

My sample data.

Here is my dax,

Actuals to prev week =

VAR selectedweek = SELECTEDVALUE(TATLOADING[CurWeekOffset])

Return

IF(ISBLANK(selectedweek),

DIVIDE(CALCULATE([percentage],FILTER(TATLOADING,TATLOADING[CurWeekOffset]=-2)),CALCULATE([percentage],FILTER(TATLOADING,TATLOADING[CurWeekOffset]=-3))),

CALCULATE([percentage],FILTER(TATLOADING,TATLOADING[CurWeekOffset]=(selectedweek-1)))

)

When I slice using selectedweek it is showing percentage of appropriate week output.

But when I am trying trying for previous week(i.e selectedweek -1) it is showing blank.

Is there anything I am missing. or is there any other way to approach it.

Thanks.

[amitchandak]

@Anonymous , Create a week/Date table. Have week rank and use that of prior week

refer

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

[Anonymous]

@amitchandak, Thanks for your response. This is working if it is static by passing our values.

But when slicer is applied, how can we execute by using weekrank.

Ex. 20Wk28 is applied in slicer we need to get value and subtract that value by -1 to get last week.

So I tried following measure.

Actuals to prev week =

VAR selectedweek = SELECTEDVALUE('Calendar (2)'[WeekSequenceNum])

Var week = CALCULATE([percentage],FILTER('Calendar (2)','Calendar (2)'[WeekSequenceNum]=MAX('Calendar (2)'[WeekSequenceNum])))

Var lastweek = CALCULATE([percentage],FILTER('Calendar (2)','Calendar (2)'[WeekSequenceNum]=MAX('Calendar (2)'[WeekSequenceNum])-1))

var weekselected = CALCULATE([percentage],FILTER('Calendar (2)','Calendar (2)'[WeekSequenceNum]=selectedweek))

var lastweekselected = CALCULATE([percentage],FILTER('Calendar (2)','Calendar (2)'[WeekSequenceNum]=selectedweek-1))

Return

IF(ISBLANK(selectedweek),

DIVIDE(week,lastweek),

DIVIDE(weekselected,lastweekselected)

)

instead of weekrank I used weekseqnum. Here the problem is how can I get value from slicer, I used selectedvalue

but its not working. It is showing blank(for last week which is a thing I need to know). How can I go through?

Thanks.

[Anonymous]

@amitchandak, While troubleshooting found the following,

Troubleshooting 1:

Created 2 measures for this week and last week.  like the following:

29 = CALCULATE([percentage],FILTER('Calendar (2)','Calendar (2)'[CurWeekOffset]=MAX('Calendar (2)'[CurWeekOffset])))

28 = CALCULATE([percentage],FILTER(ALL('Calendar (2)'),'Calendar (2)'[WeekSequenceNum]=MAX('Calendar (2)'[WeekSequenceNum])-1))

Actual value = [29]/[28]

It gives actual values.

Troubleshooting 2:

Straightaway applied the measure instead of seperate measure.

Actual value = Divide(

CALCULATE([percentage],FILTER('Calendar (2)','Calendar (2)'[CurWeekOffset]=MAX('Calendar (2)'[CurWeekOffset])))

,CALCULATE([percentage],FILTER(ALL('Calendar (2)'),'Calendar (2)'[WeekSequenceNum]=MAX('Calendar (2)'[WeekSequenceNum])-1)))

It produces blank when filtered. Its weird, let me know anything which I made any error.

Thanks.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? 

[Anonymous]

@amitchandak,

My sample data,

Table 1:

Hour	Weekname	Count_loading_col	Total_loading	Total teus	Location_name	Column	Curweekoffset	Main flag
41	20WK24	0	0	90		0	-6	0
4	20WK24			80		ND	-6	0
	20WK24				a	ND	-6	0
8	20WK24	0	0	90		0	-6	0
71	20WK24	0	0	80	b	0	-6	0
3	20WK24	22	1	90		1	-6	0
21	20WK25	0	0	90		0	-5	0
13	20WK25	0	0	88		0	-5	0
	20WK25					ND	-5
4	20WK25				c	ND	-5	0
41	20WK25	0	0		d	0	-5	0
7	20WK25	0	0			0	-5	0
	20WK25					ND	-5	0
47	20WK25	0	0	80	b	0	-5	0
11	20WK25	0	0	80		0	-5	0
23	20WK25	0	0		e	0	-5
9	20WK25	0	0		b	0	-5	0

Table 2:

Weekname	CurWeekOffset	WeekSequenceNum	MonthLong	Year
20WK19	-11	19	May	2020
20WK20	-10	20	May	2020
20WK21	-9	21	May	2020
20WK22	-8	22	May	2020
20WK23	-7	23	June	2020
20WK24	-6	24	June	2020
20WK25	-5	25	June	2020
20WK26	-4	26	June	2020
20WK27	-3	27	June	2020
20WK28	-2	28	July	2020
20WK29	-1	29	July	2020
20WK30	0	30	July	2020
20WK31	1	31	July	2020
20WK32	2	32	August	2020

Both the tables are connected using weekname.

Calculated percentage,

percentage = DIVIDE(SUM(TATLOADING[Main flag]),COUNT('TATLOADING'[Main flag]))

By using percentage measure I can calculated this week percentage by last week percentage using weekseqnumber in calendar table.

Both the measure is defined in above post.