Forum Discussion
Previous week calculation without relative date
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. ...
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.
Anonymous ,Can you share sample data and sample output in table format?
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.