问题

以下是示例数据集

数据集如下：

The data has two slicers ( date and category ) shown below

数据包含两个切片器（日期和类别），如下所示

我正在编写一个DAX语句，只有在日期范围在当前年份 2023 内时才能将 sum(values) * 10 相乘。

我正在编写一个DAX语句，只有在日期范围在当前年份 2023 内时才能将 sum(values) * 10 相乘。

The StartYear gives the start of the current year, firstD gives the lowest date from the date slicer.

StartYear 给出了当前年份的开始，firstD 给出了日期切片器中的最早日期。

Formula = 
var new = sum(Test[Value]) * 10
var startyear = DATE(YEAR(TODAY()),1,1) 
var firstD = CALCULATE( MIN( Test[Date]), ALLSELECTED(Test[Date]) )
return if( ISFILTERED(Test[Categories]) && firstD >= startyear, new, 0 )

Now when I filter dates to 2023, the total value should be 2300 but it shows as 0

现在，当我将日期筛选到2023年时，总值应该为2300，但显示为0

However the DAX works when I select A or B

然而，当我选择A或B时，DAX有效

If we remove the ISFILTERED function then, it gives wrong value, the expected value is 0 because the start date is in 2022, but it shows 650

如果我们删除ISFILTERED函数，那么它会给出错误的值，期望值为0，因为开始日期在2022年，但它显示为650

let me know if that is the right syntax

让我知道这是否是正确的语法

Formula = 
var new = sum(Test[Value]) * 10
var startyear = DATE(YEAR(TODAY()),1,1) 
var firstD = CALCULATE( MIN( Test[Date]), ALLSELECTED(Test[Date]) )
return if( ISFILTERED(Test[Categories]) && firstD >= startyear, new, 0 )

答案1

得分: 2

抱歉，我理解您只需要翻译代码的部分。以下是翻译后的代码部分：

It looks like you are not using a separate calendar table to handle this, which you need!

In your very last example you have set your slicer to some time late 2022, but the minimum value of 'Test'[Date] for your selected category is in year 2023. Hint: set the slicer to e.g. 2022-12-14, this will include a 2022-date for Category A in your data.

Your measure behaves exactly how it is supposed to, in other words!

To fix this, you need to do the following:

 1. Create a calendar table in your model, this should contain contiguous dates, which is necessary for the filtering method you want
 2. Establish a relationship between the calendar table and existing 'Test' table. 
 3. Use the date column from your new calendar table in your slicer and as date reference in your measure

Exactly how to create a calendar table is thoroughly documented on Google, I recommend you search and find an article or video you understand for implementing this.

Lastly: Your use of 'ISFILTERED' in this measure seems strange, since you mention nowhere the requirement of only showing a number if the column you are testing filtering on is filtered, if that makes sense.. :-) The way you describe your calculation, you **only** need to check whether the selected date range starts in the current year.