Calculating previous date data

[#1]

I am trying to determine a count of Issue ID of a previous Load Date.  The Load Dates are Thursday dates.  I have attached a file with the Load Date and the Issue IDs I am working with.  I do have duplicates in the source data, so I have been trying to use DISTINCT in the formula.

This is what I am trying so far and it is only giving me the current value:

=Num(count(Distinct{$<[Load Date]={">=$(=WeekStart(Addweeks(Today(),-1)))<=$(=WeekEnd(AddWeeks(Today())))"}>} [Issue ID]),'0')

The -1 Total should be the total from the previous week, but as you can see, it is the current total repeated.

My load statement for the Load Date is ensuring that the Load date is an actual Date:

LOAD Date(Num([Load Date]), 'MM/DD/YYYY') AS [Load Date],

Any help would be greatly appreciated!

[vishsaggi]

How did you get 914 there? Is that the correct total for current load date? For me the current load date count is coming 808? What is the total you are expecting in your -1Total column ?

[#3]

That is data from something else.  I would need to see 808 as the current count and 826 as previous count.  so then the arrow would be green and going up.  (I have the calculation to do the arrow, but cannot get the previous count.

[vishsaggi]

Oh got it. Ok will work on it once i reach office and get back.

[#5]

Thank you so much!

[vishsaggi · Accepted solution]

Try this and let me know.

Current Expr:    = Count(DISTINCT {< LoadDate = {'$(=Max(LoadDate))'} > } IssueID)

Previous Expr:  = Count(DISTINCT {< LoadDate = {'$(=Max(LoadDate,2))'} > } IssueID)

[#7]

Perfect!!  Thank you so very much!