Forum Discussion

ellevy's avatar
ellevy
Icon for Helper I rankHelper I
5 years ago
Solved

previous row calculations

Hi   i have a data of the following( for example) year |  week in year | no. of people 2017        1                       500 2018        1                        300 2017        2          ...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    ellevy try this measure for previous year

     

    Prev Year and Week = 
VAR __currentYear = MAX ( Table[Year] )
VAR __prevYear = __currentYear - 1
VAR __currentWeek = MAX ( Table[Week in Year] )
RETURN
CALCULATE (
SUM ( Table[Number of People] ),
Table[Year] = __currentYear,
Table[Week in Year] = __currentWeek
)

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

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

ellevy , Create a column

Week Year = [Year]*100 + [Week]

 

Create a week year table with year, week, and Week Year table.   Assume Date

 

Example measure

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

For Week comparison you can create Week rank column in your table

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

 

measures like

 

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

 

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

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