You generally set a variable (repository variable refreshing every few hours or even just once per day, depending on the different timezones you must cover).
The query sourcing the variable will use your own time dimension to get the value of the fiscal week column based on the current date.
Or just using the same logic to filter your week based on the result of a dimensional only analysis filtering current date and return fiscal week.
If you have this modeled into your time dimension (since "Fiscal Week" is no absolute concept like the month of "January") then it simply becomes an attribute on the one hand and also an alternative roll-up hierarchy type within your time dimension and you can drill total -> Fiscal Year -> Fiscal Quarter -> Fiscal Week -> Day for example.
MUCH better approach than any SSQL statement because it's data and not code.