Same Day Previous Week

[Anonymous69]

Hi ,

I'm putting together a report, and I have a sales column and a date column. How does one derive the previous Day last week?

This figure will eventually be used to derive an average of the same day per week for the last 5 weeks, but if you can help with the same day the previous week, I could do the remaining myself.

Thanks in advance.

[Kushal_Chawda]

@Anonymous69  It depends on how your week is aligned and setup of your report. We need more information here as to how your report should look like with your requirements.  In general, you can use below set for getting same day previous week

Date ={"$(=max(Date)-7)"}

[seanbruton]

Please try this solution ....

I have a master calendar that drives all the variables and dates from this qvd. This ensures that if there is a data break, the last qvd will determine the date for all the numbers. This is then joined onto the data model onto for that key date. This same principle can be used on the front end.

I use a script that calculates on a chart the same day for the previous 52 weeks. the below script is is a few lines, these fields are run through a crosstable. The vGetdateV2() is the calendar date.

Let vToday= date(Today()-1,'YYYY-MM-DD'); // test today

Let vGetDatev2 = '$(vToday)';

if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)' ,1,0) as SameDayRolling_P0_Ind,
if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)'-7 ,1,0) as SameDayRolling_P1_Ind,
if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)'-14 ,1,0) as SameDayRolling_P2_Ind,
if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)'-21 ,1,0) as SameDayRolling_P3_Ind,
if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)'-28 ,1,0) as SameDayRolling_P4_Ind,
if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)'-35 ,1,0) as SameDayRolling_P5_Ind,

[Anonymous69]

Thanks, both date column and sales are in the same table . I have been able to write the measure you supplied but how so I say sum sales when date.autocalendar.date = Date ={"$(=max(Date)-7)"}.

[Anonymous69]

Thanks. Can this not be done in measure to say calculate sales value when date = date-7. Please not Today function is not useful for this as the dates are mostly in the past. 7 days from the current sale date.

[seanbruton]

Hi,

Yes, you can, you can call a variable to control the date, or use peek to get the value from a table, or an if statement from the calendar with this value.

[Anonymous69]

Thanks. Do you have a measure example?

[seanbruton]

sum(if(num(WeekDay(CalcDate)) = num(WeekDay('$(vGetDatev2)')) and CalcDate <= '$(vGetDatev2)'-7 , measurefield ,0))