英文:
DAX: Projected sum for all months greater than current month of current year
问题
我有一个要求，需要过滤所有月份 >= 当前年份的销售总额以及我们有销售预测数据的任何后续年份。
所以对于2023年，我只需要从当前月份（即2月）到2023年12月的销售总额。
我的DAX如下，可以准确完成当前年度或当前月份的过滤，但我似乎无法同时过滤两者，否则结果将包括所有之前的年份。客户希望在同一可视化中看到过去销售和预测销售，所以我不能只是过滤掉之前的年份。
当前销售总额 = 计算(SUM('表'[销售]), 过滤('日期表', YEAR('日期表'[日期]) >= YEAR(TODAY())))
谢谢您的任何帮助！
英文:
I have a requirement to filter a sum of projected sales for all months >= current month of the current year and any subsequent years that we have project sales data for.
So for 2023 I'd need the sum of projected sales for only February (the current month) through December 2023 on.
My DAX below accomplishes this accurately for current year OR current month, but I can't seem to filter by both without the results including all prior years. Client wants to see past sales and projected sales in the same visual so I cannot just filter out the prior years.
CURRENT SUM OF SALES = CALCULATE(SUM('Table'[Sales]), FILTER('DateTable', YEAR('DateTable'[Date]) >= YEAR(TODAY())))
Thank you for any help!
答案1
得分: 1
以下是翻译好的部分：
首先，让我回答您有关编写一个筛选所有大于等于当前年份当前月份的DAX的请求；您应该按照以下方式创建日期：
DATE(YEAR(NOW()),MONTH(NOW()),1)
然后按照以下方式将其添加到您的度量中：
当前销售总额 = CALCULATE(SUM('表'[销售]), FILTER('日期表', '日期表'[日期] >= DATE(YEAR(NOW()),MONTH(NOW()),1)))
其次，关于您的客户希望在同一可视化中看到过去销售和预测销售的担忧，您只需创建另一个显示过去销售的度量。
如果我正确理解您的表结构，这个月的销售值将在下个月的数据刷新时更新为实际值；如果不是，请告诉我。
如果我的假设成立，您可以按照以下方式编写“过去销售”度量：
过去销售 = CALCULATE(SUM('表'[销售]), FILTER('日期表', '日期表'[日期] < DATE(YEAR(NOW()),MONTH(NOW()),1)))
然后，在一个折线图中使用这两个度量，以'日期表'[日期]列作为X轴。
希望我的回答有所帮助；如果是的话，请标记为答案并投票支持它 
英文:
First, let me answer your request on writing a DAX that filters all months >= current month of the current year; you should create your date as follow:
DATE(YEAR(NOW()),MONTH(NOW()),1)
> Then add it to your measure as follow:
CURRENT SUM OF SALES = CALCULATE(SUM('Table'[Sales]), FILTER('DateTable', 'DateTable'[Date] >= DATE(YEAR(NOW()),MONTH(NOW()),1)))
Second, regarding your concern about your client wanting to see past sales and projected sales in the same visual, you only have to create another measure that shows past sales.
> If I understand your table structure correctly, this month's sales value will be updated to the actual value when your ETL refreshes the data next month; if not, please let me know.
>> If my assumption is valid, you can write Past Sales - measure as follows:
Past Sales = CALCULATE(SUM('Table'[Sales]), FILTER('DateTable', 'DateTable'[Date] < DATE(YEAR(NOW()),MONTH(NOW()),1)))
>> Then, use both measures in a Line chart with the 'DateTable'[Date] column as the X-axis.
I hope I helped in a way; if so, please mark this as an answer and vote for it
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论