Comparing Dates from a Table to a Measure

[landrade24]

Hello,

I'm trying to do something which I thought would be simpler but I'm actually not being able to get it to work. The goal is to filter the dates in multiple charts with a single button. Some charts will display data for the week I chose, other charts will display data for that week and also the 3 weeks before.

I have a table with a Date column. I have a button/slicer whose selected value I'm storing as a measure ('WeekValue'). I'm trying then to create 2 new measures: one which would be equal to 1 if the week number for a given date is equal to the WeekValue I chose. The other measure should be equal to 1 if the Week Value minus the week number for that date is between 0 and 3.

This is how the SelectedWeekFilter measure looks now:

SelectedWeekFilter =

VAR chosenWeek = WeekValue[WeekValueNew Value]

VAR refWeek = WEEKNUM(MAX(Table[Date]),2)

VAR result =

if(refWeek=chosenWeek,1,0)

return result

Then I have my chart use the filter SelectedWeekFilter = 1.

I'd understood that using the MAX aggregator for the Date would allow the comparison to be evaluated for each row of my table, but I might be wrong about that. This measure doesn't work. Does anyone know what I'm doing wrong? I can use a different logic, as long as I'm able to filter data based on my defined WeekValue. I appreciate any help here.

Thank you

PS - My first attempt actually was to do something as simple as creating a calculated column and comparing then each weeknum to my external WeekValue. Such as in IF(Table[WeekNum]=WeekValue[WeekValueNew Value],1,0), evaluated in each row. I've seen examples in this forum where users compare a value in each row to a measure, but for some reason this didn't work either for me.

[landrade24]

Hello @amitchandak ,

Thank you very much for the suggestion. 

Question -- following this logic, for each quantity I want in my charts, I would have to create a respective This Week measure, right? And so forth for any other time period.

[amitchandak · Accepted solution]

@landrade24 , For week have these columns in date table

Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
WeekDay = weekday([Date],2)
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

then you can measures like

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

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

Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510