Formula - Prior Week

[Anonymous]

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 what the total count was, one week ago.   Tried several formulas but it keeps returning a "blank" value.    Any help is appreciated!

Cumulative RMA Count =

VAR CurrentDate= MAX( 'Date Table'[Date])

RETURN CALCULATE([Distinct Count of RMAs], ALL('Date Table'[Date]), 'Date Table'[Date]<=CurrentDate)

[Ashish_Mathur]

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]

@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]

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.