Previous Value for a weeks start date

[UditJ]

Need to show in a column the previous value for the dates shown in the iamge below. For example, the 5/24/2021 will show no data and 5/31/2021 will show the previous value which is 66,283,907. Dates are calculated as 

Week Start Date = 'Reporting Dashboard_Social'[Date] - WEEKDAY('Reporting Dashboard_Social'[Date],2) + 1

Need a similar function like sameperiodlastyear but instead of year need it for start day of the week. 

Any suggestions? 

[Greg_Deckler]

@UditJ It's essentially this pattern.

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[UditJ]

What if i want to show last years weeks values ? so a new column showing last year weeks values. Last year week can be as close as possible. Any suggestions ?

[v-rzhou-msft · Accepted solution]

Hi @UditJ ,

I suggest you to create a DimDate table to help your calculation.

DimDate =
VAR _STEP1 =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "Week Start Date",
            [Date] - WEEKDAY ( [Date], 2 ) + 1
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "Year of Week", YEAR ( [Week Start Date] ),
        "WEEKNUM", WEEKNUM ( [Week Start Date] ) - 1
    )
RETURN
    _STEP2

Measure:

Previous Week Impression =
VAR _PREVIOUWEEK =
    MAXX (
        FILTER (
            ALL ( DimDate ),
            DimDate[Week Start Date] < MAX ( DimDate[Week Start Date] )
        ),
        [Week Start Date]
    )
RETURN
    CALCULATE (
        SUM ( 'Reporting Dashboard_Social'[Impressions] ),
        FILTER ( ALL ( DimDate ), DimDate[Week Start Date] = _PREVIOUWEEK )
    )

Previous Year Week Impression = 
CALCULATE (
    SUM ( 'Reporting Dashboard_Social'[Impressions] ),
    FILTER (
        ALL ( DimDate ),
        DimDate[Year of Week]
            = MAX ( DimDate[Year of Week] ) - 1
            && DimDate[WEEKNUM] = MAX ( DimDate[WEEKNUM] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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