Hi All
I've got a table of sales data stored by week and I want to show this on a line chart. Sales update overnight and I only want to show on the chart sales up to the last complete week. I can't seem to get it to stop pulling the partial week sales through. I currently have this measure:
1. Chart sales =
2. calculate(sum(Sales_Table[Sales]),
3. all('Current Week'[Current Week]), all('Master Fiscal Calendar'[Fiscal Week]),
4. filter(all('Master Fiscal Calendar'[Numerical Fiscal Week]), 'Master Fiscal Calendar'[Numerical Fiscal Week] <=
5. ( lookupvalue('Master Fiscal Calendar (2)'[Numerical Fiscal Week 2], 'Master Fiscal Calendar (2)'[Fiscal Week], SELECTEDVALUE('Master Fiscal Calendar'[Fiscal Week]) )
6. ))
7. )
Line 3 is just clearing some other filters I have on the page. Line 4 I think clears the filters I have on tyhe numerical fical week field and then looks for values <= Line 5. All of this I think I understand, except Line 5 doesn't work.
Line 5 should be taking the selected value from the current 'Master Fiscal Calendar'[Fiscal Week] selection which is text, using that to look up to the column in the calendar table (again stored as text) and returning the week number but stored as a number this time, not as text.
The result of the selected value formula is text, and the lookup is definitelty returning a number. If I swap out the selected value for a hard typed "202047" (so text) then I get the behavour I want. Similarly, if I swap out the lookup for 202047 (hard typed as a number) I also get the behavoiur that I want. I'm just at a bit of a loss as to why I can't get this to work..
Am I missing something obvious? Or is there a better way?
