Forum Discussion

nicholastrausch's avatar
nicholastrausch
Frequent Visitor
7 years ago

Dynamic Week Offset based on Slicer Value

Ok.. been staring at this for too long and need help.  I have a date table where I use a Week Counter and then Calculate to determine the Week Counter value for current week based upon TODAY() being equal to a date in the table.  It then offsets (0, -1, -2, -3, etc) accordingly.  Need to add a little complexity to this though, in fact that I need to be able to pass in the date that I want to set as the "0" baseline through a slicer value. I have created a parameter table and can pass the value (trying to swap in DTM value for TODAY() ), but it errors out.  The result I'm trying to get is for the [Dynamic Week] field below.  Can anyone advise on how to take the Relative Week column below and have it accept a value from a slicer?

 

Week Counter = CALCULATE(DISTINCTCOUNT('Week Relative LUT'[ISO Year Week]),all('Week Relative LUT'),'Week Relative LUT'[ISO Year Week]<=EARLIER('Week Relative LUT'[ISO Year Week]))
Relative Week = 'Week Relative LUT'[Week Counter] -
                CALCULATE(
                    SUM('Week Relative LUT'[Week Counter]),
                    All('Week Relative LUT'),
                    TODAY()='Week Relative LUT'[Date])

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there. Would you please be more descriptive about this problem? Also, would you please format code using https://www.daxformatter.com/? However, from what I understand, you want to have something dynamic that will change based on a value taken from another table (slicer). Well, in such a case you have to create a measure, there is no way to put it in a column. Please remember that a column creates (static) values when the model is loaded/refreshed. You can only have a measure that will respond to selections in a slicer.