Find count for a week ago, syntax help

[aashton]

So I want to display a list of dates, and what the ticket count was one week before that date.  If it's Sat, Sun or Monday, give me the one week ago from the Friday before.  I've made this date column that works:

Week ago exc Weekends = IF('Date'[DayOfWeek]="Sunday", DATEADD('Date'[Date], -9, DAY), IF('Date'[DayOfWeek]="Monday", DATEADD('Date'[Date], -10, DAY), DATEADD('Date'[Date], -8, DAY)))

I just can't get the syntax right for the ticket count.    Any syntax I try is not working...

I've tried, 

Ticket Count Week Ago = CALCULATE([Total Tickets], FILTER('Date', 'Date'[Date]='Date'[Week ago exc Weekends]))

....doesn't work.  Please help.

[Anonymous · Accepted solution]

Hi @aashton ,

It seems that you just want to calculate value of the day that is a week ago, right?

I have created a data sample like this:

Then you could use the following formula to create a column or a measure.

1. Column:

Column = CALCULATE(SUM('Date'[Tickets]),FILTER('Date','Date'[Date]=EARLIER('Date'[Week ago exc Weekends])))

Or if one day only has one row, you could use LOOKUPVALUE():

Column 2 = LOOKUPVALUE('Date'[Tickets],'Date'[Date],[Week ago exc Weekends])

 2.Measure:

Measure(total is wrong) = CALCULATE(SUM('Date'[Tickets]),FILTER(ALL('Date'),'Date'[Date]=MAX('Date'[Week ago exc Weekends])))

Measure = IF(HASONEVALUE('Date'[Date]),[Measure(total is wrong)], SUMX('Date',[Measure(total is wrong)]))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@aashton  use SELECTEDVALUE(DAY)="Sunday .... so on , it should work. and this data should be in a table visual