Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.   ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak,

       

      My sample data,

      Table 1:

      HourWeeknameCount_loading_colTotal_loadingTotal teusLocation_nameColumnCurweekoffsetMain flag
      4120WK240090 0-60
      420WK24  80 ND-60
       20WK24   aND-60
      820WK240090 0-60
      7120WK240080b0-60
      320WK2422190 1-60
      2120WK250090 0-50
      1320WK250088 0-50
       20WK25    ND-5 
      420WK25   cND-50
      4120WK2500 d0-50
      720WK2500  0-50
       20WK25    ND-50
      4720WK250080b0-50
      1120WK250080 0-50
      2320WK2500 e0-5 
      920WK2500 b0-50

       

      Table 2:

      WeeknameCurWeekOffsetWeekSequenceNumMonthLongYear
      20WK19-1119May2020
      20WK20-1020May2020
      20WK21-921May2020
      20WK22-822May2020
      20WK23-723June2020
      20WK24-624June2020
      20WK25-525June2020
      20WK26-426June2020
      20WK27-327June2020
      20WK28-228July2020
      20WK29-129July2020
      20WK30030July2020
      20WK31131July2020
      20WK32232August2020

       

      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.