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.

 

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.
 

 

Need Help

5 Replies

    • 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.