YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

huangapple go评论64阅读模式
英文:

YTD Measure not working in DAX when the date slicer is set to previous year

问题

以下是测试数据:

YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

以下是日期数据:

Dates = CALENDAR(date(2022,1,1),TODAY())

表之间的关系:
YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

问题:创建一个度量,显示本年度的累计总金额,即使日期切片器从前一年开始:

以下是我的代码:

YTD = 
VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
var startyear = DATE(YEAR(TODAY()),1,1)
return if( minim >= startyear , 
calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear))  ,  
 calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear)) )

测试场景1正常工作:显示正确的信息,日期切片器从2022年开始,度量显示本年度的YTD结果

YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

测试场景2:不工作,显示40而不是130
YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

测试场景3:不工作,即使年份是当前年份
YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

英文:

Here is the test data:

YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

Here is the date Data:

Dates = CALENDAR(date(2022,1,1),TODAY())

Relationship between the tables:
YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

Question: Create a Measure that shows the YTD Total amount for the current year, even if the date slicer starts from the previous year:

Here is my code:

YTD = 
VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
var startyear = DATE(YEAR(TODAY()),1,1)
return if( minim >= startyear , 
calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear))  ,  
 calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear)) )

Test Scenario 1 Works fine: Shows correct info, the date slicer starts at 2022, and the measure shows the YTD result of the current year

YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

Test Scenario 2: Does Not Work, Shows 40 instead of 130
YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

Test Scenario 3: Does not work, Even when the year is the current year
YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

答案1

得分: 1

替换 AllSelectedAll,因为 AllSelected 会保留切片器的筛选条件。

YTD = TOTALYTD(sum(test[Totals]),all(Dates[Date]))

英文:

Instead of AllSelected change to All since all selected keeps the slicer filter

YTD = TOTALYTD(sum(test[Totals]),all(Dates[Date]))

答案2

得分: 1

The YTD formula will help you get the correct output/result.

YTD =
VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
VAR startyear = DATE(YEAR(TODAY()),1,1)
return if( minim >= startyear ,
CALCULATE(SUM(test[Totals]), test[Date] >= startyear, REMOVEFILTERS(Dates[Date])),
CALCULATE(SUM(test[Totals]), test[Date] >= startyear, REMOVEFILTERS(Dates[Date])) )

Simply you can use the following formula for the YTD.

YTD = CALCULATE(SUM(test[Totals]), YEAR(test[Date]) = YEAR(TODAY()), REMOVEFILTERS(Dates[Date]))

英文:

The YTD formula will help you get the correct output/result.

YTD = 
    VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
    VAR startyear = DATE(YEAR(TODAY()),1,1)
    return if( minim >= startyear , 
            CALCULATE(SUM(test[Totals]), test[Date] >= startyear, REMOVEFILTERS(Dates[Date])),  
            CALCULATE(SUM(test[Totals]), test[Date] >= startyear, REMOVEFILTERS(Dates[Date])) )

YTD度量在DAX中不起作用,当日期切片器设置为上一年时。

Simply you can use the following formula for the YTD.

YTD = CALCULATE(SUM(test[Totals]), YEAR(test[Date]) = YEAR(TODAY()), REMOVEFILTERS(Dates[Date]))

huangapple
  • 本文由 发表于 2023年4月11日 06:33:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/75981212.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定