Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Formula - Prior Week

I have a measure that tracks the amount of Returns  (by Return number...which is a text value).    This works fine for tracking current number of RMAs  (returns).     However, I need to determine w...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

Try this measure

=calculate([Distinct Count of RMAs],datesbetween('Date Table'[Date],MAX( 'Date Table'[Date])-7,MAX( 'Date Table'[Date])-7))

In the date slicer (created from the Date Table, select any date.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Ashish_Mathur     Unfortunately Ashish that still returned a blank value.   

     

    I should clarify that I am looking for a cumulative total.   If you look at the small segment of a bar chart I have, you will see that we have just entered Week 40  (today).     Last week, the total return count was 869.   The week before (week 38) it was 844.  

     

    So technically, the formula should return 869 (that was the cumulative total for week 39, the previous week), since we are now in week 40.   So when I say "last week" it is just not just a simple "today minus 7 days" as that would be a rolling week.   I need the cumulative total.    

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      I will need your PBI file to work with.  Also, in the Calendar Table, please ensure that you have a column for the week number.