Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.