cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
landrade24
Regular Visitor

Comparing Dates from a Table to a Measure

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.
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
landrade24
Regular Visitor

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
Super User
Super User

@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

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors