Forum Discussion

UditJ's avatar
UditJ
Helper III
3 years ago
Solved

Previous Value for a weeks start date

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? 


Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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/339586.
    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's avatar
      UditJ
      Helper III
      3 years ago

      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 ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.