Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have been trying to get this to work for ages but can't seem to incorporate today's date in my week calculation.
Essentially I want it to look at todays date dynamically, check what week number this day is in and bring me back the quantity for the week before that.
This is what I have;
Solved! Go to Solution.
Basically you should use variables. Meta code here:
Var thisweek = weeknumber(today())
Var amount = calculate(xxx,weeknumber[date]=thisweek-1)
Return amount
This will only work for week 2 and up in a year. If you want a better solution covering multiple years you need to introduce a YearWeek column in your dates table.
Final Caveat: TODAY() uses UTC in the service - this can lead to unexpected results depending where you live.
What should happen when today's date is Jan 1st?
Didn't even think of that, but would love it to brink back the last week of the previous year!
Basically you should use variables. Meta code here:
Var thisweek = weeknumber(today())
Var amount = calculate(xxx,weeknumber[date]=thisweek-1)
Return amount
This will only work for week 2 and up in a year. If you want a better solution covering multiple years you need to introduce a YearWeek column in your dates table.
Final Caveat: TODAY() uses UTC in the service - this can lead to unexpected results depending where you live.
Thanks so much, I think i have it, it seems to not respond to my date slicers which is good, until there is no data.
For instance, If I change the date slicers to full year 2020, or first 6 months it works, but if I change it to July for instance, the measure goes blank, any idea why? Is it because I'm filtering out the data for it to calculate?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |