Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

This week date measure

Hi , can we create a measure to return all the dates that belongs to this week ? and last year same week ?    this week dates = var todaydate = TODAY() var startdate= CALCULATE(SUM(DimTimeCale...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you want the matrix to show the values for this week and the previous week.

    Here's my solution.

    1.Create a calendar table.

    Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

     

    2.Create a measure, put it into visual level filters and set show items when the value is 1.

    Measure = var _thisweek=WEEKNUM(TODAY(),2)
var _week=WEEKNUM(MAX('Calendar'[Date]),2)
return IF(_thisweek=_week||_thisweek-1=_week,1)

     

    As shown above, the view shows the data for this week and the previous week.

     

    Best Regards,

    Stephen Tao

     

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

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

Anonymous . Last year same weekday or same week

measure

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

 

OR new columns in date table 


Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

measures
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)))

or

 

Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -53)))

 


Last year same week  = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

 

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