Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper V
5 years ago
Solved

Find count for a week ago, syntax help

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.