Forum Discussion
Helper ICalculating Latest Weeks & Comparing to Same Timeframe PY
Super UserL1102 , You need to have the week Number or year week column in the date table.
These will help in grouping
Week Number = WEEKNUM([Date],2)
WeekDay = weekday([Date],2) // Monday Start user 1 for Sunday Start
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
For WOW
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
Measures
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))
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)))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
L1102Hi amitchandak ,
I'm not sure if grouping in this way is going to help. Right now I have the latest weeks being called with the DAX I have, but my measures that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do I need to create all new measures? ideally the user has the ability to choose if they want to select specific weeks in a seperate slicer or use the latest timeframes slicer depicted below. I'm just not sure how to calculate the current year and prior year to allow for this, or even if it's possible.
I've also created this calendar that i'm trying to use for time intelligence