英文:
Previous Week Measure - Week 52 2019 and Week 1 2020
问题
以下是翻译好的部分：
"我目前使用以下方法来获取上一周的值，但由于现在是新一年的第一周，该字段返回“空白”，因为找不到任何上一周的数据。
我该如何调整我的方法，以包括类似于...如果是第一周，则使用上一年的最后一周，如果不是，则使用上一周的情况。
VAR CURRENT_WEEK = WEEKNUM(TODAY()) return
CALCULATE(AVERAGE(DATA_TABLE[VALUE]),
FILTER (DATA_TABLE, WEEKNUM(DATA_TABLE[DATE]) = CURRENT_WEEK -1))
提前感谢您的帮助。"
英文:
I currently use the below measure to retrieve a value for the previous week, however as it's now week 1 of a new year, the field is returning "blank" as it cannot find any previous weeks.
How would I adapt my measure to include something along the lines of... if week 1 use the last week of the previous year, if not use the previous week.
VAR CURRENT_WEEK = WEEKNUM(TODAY()) return
CALCULATE(AVERAGE(DATA_TABLE[VALUE]),
FILTER (DATA_TABLE, WEEKNUM(DATA_TABLE[DATE]) = CURRENT_WEEK -1))
Thanks in advance for your help
答案1
得分: 1
我建议使用一个起始日期来计算周数。例如，如果您选择2018年1月1日作为起始日期，那么2018年1月1日至7日将是第1周，而2020年的第一周将是第105周。这将解决您的问题。
如果您的数据跨越多年，您正在使用的方法将变得非常难以处理。周数1将表示2020年、2019年和所有其他年的第1周。这将混淆计算结果。上述方法将确保您为每周获得一个唯一的编号。
英文:
I would suggest using a start date and calculating the weeknum from that date. For example, if you choose 1 Jan 2018 as start date, then 1-7 Jan 2018 would be week 1 and first week in 2020 would be week 105. This would solve your issue.
The method you are using becomes really hard to handle if your data has multiple years. Weeknum 1 would denote the 1st week of 2020, 2019 and all the other years. This will mess up the calculation. The above method will make sure you have a unique number for a week.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论