Hi, I have created a Calculated Table for Dates: Date = ADDCOLUMNS (CALENDAR (DATE (2021, 01, 01), Today()),"Year", YEAR([Date]),"MonthNumber", MONTH([Date]),"Month", FORMAT([Date],"mmmm"),"WeekNumber", FORMAT(WEEKNUM([Date],2),"00"),"Quarter", QUARTER([Date]),"DayOfWeek", WEEKDAY([Date])) I also have a measure called Quantity TY (for current year) that is: QTY TY = CALCULATE(sum('Merge_Table'[Quantity]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[WeekNumber] = Max('Date'[WeekNumber]) )) And another measure for QTY LY (For last year) that is: QTY LY = CALCULATE(sum('Merge_Table'[Quantity]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[WeekNumber] = Max('Date'[WeekNumber]))) The problem is that the QTY LY is returning the wrong week last year. I either need it to be 1 week forward or 1 week backwards and I won't know the answer until I can try both options. Can someone help me here?

Anonymous , The code seems fine , if not then you can try week rank way Have these new columns in Date Table, Week Rank is Important in Date/Week Table Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense) ORWeek Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format These measures can helpThis 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-98c30fab69d3https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123https://www.youtube.com/watch?v=pnAesWxYgJ8Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

Anonymous

Not applicable

3 years ago

Solved

Time Intelligence

Hi,

I have created a Calculated Table for Dates:

Date =

ADDCOLUMNS (

CALENDAR (DATE (2021, 01, 01), Today()),

"Year", YEAR([Date]),

"MonthNumber", MONTH([Date]),

"Month", FORMAT([Date],"mmmm"),

"WeekNumber", FORMAT(WEEKNUM([Date],2),"00"),

"Quarter", QUARTER([Date]),

"DayOfWeek", WEEKDAY([Date])

)

I also have a measure called Quantity TY (for current year) that is:

QTY TY = CALCULATE(sum('Merge_Table'[Quantity]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[WeekNumber] = Max('Date'[WeekNumber]) ))

And another measure for QTY LY (For last year) that is:

QTY LY = CALCULATE(sum('Merge_Table'[Quantity]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[WeekNumber] = Max('Date'[WeekNumber])))

The problem is that the QTY LY is returning the wrong week last year. I either need it to be 1 week forward or 1 week backwards and I won't know the answer until I can try both options.

Can someone help me here?

Need Help

amitchandak
3 years ago
Anonymous , The code seems fine , if not then you can try week rank way

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

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

These measures can help
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
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

3 Replies

amitchandak
Super User
3 years ago
Anonymous , The code seems fine , if not then you can try week rank way

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

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

These measures can help
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
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
- Anonymous
  Not applicable
  3 years ago
  Hi amitchandak
  
  Before I try that See what my results are from my calculated Date table. I expect 6/11-6/17 to all show as week 24 but only 6/11 shows that and the other 6 days as week 25.
  - Anonymous
    Not applicable
    3 years ago
    Does anyone have an idea on how to fix this calendar?

Forum Discussion

Time Intelligence

3 Replies

Related content

Interesting effects with calendar-based time intelligence

Issue with SAMEPERIODLASTYEAR and Manual Time Intelligence

Real-Time Datasources for Real-Time Intelligence.

Real Time Intelligence - Dashboard Refresh Rate

Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc