Forum Discussion

sraj's avatar
sraj
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

Comparison based on current year week vs same week last year

Hi,

 

I dont have any data yet to provide but would like to know if this is possible and how would I do it?

 

 

Thanks!

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    4 years ago

    Hi sraj,

     

    Is your issue solved? If the issue has been solved, please adopt the solution to help others.

    You need a weeknum column to define the same period last year.

    Just like:

    Weeknum = 
CONCATENATE(YEAR('Table'[Date]),WEEKNUM('Table'[Date]))

    Create measure as:

    Measure = 
var _weeknum=CONCATENATE(YEAR(MAX('Table'[Date]))-1,MAX('Table'[Weeknum]))
return
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Weeknum]=_weeknum
    )
)

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    sraj , same week day is 364 days behind

     

    example, with help from date table

    Week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

     

    how to deal with week vs week refer to my blog

     

    It need a new column for week rank in date Table

     

    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    measures examples
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

     

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi sraj,

     

    Is your issue solved? If the issue has been solved, please adopt the solution to help others.

    You need a weeknum column to define the same period last year.

    Just like:

    Weeknum = 
CONCATENATE(YEAR('Table'[Date]),WEEKNUM('Table'[Date]))

    Create measure as:

    Measure = 
var _weeknum=CONCATENATE(YEAR(MAX('Table'[Date]))-1,MAX('Table'[Weeknum]))
return
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Weeknum]=_weeknum
    )
)

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!