Forum Discussion

landrade24's avatar
landrade24
Regular Visitor
3 years ago
Solved

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.
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

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

  • landrade24's avatar
    landrade24
    Regular Visitor
    3 years ago

    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.