英文:
YTD Measure not working in DAX when the date slicer is set to previous year
问题
以下是测试数据:
以下是日期数据:
Dates = CALENDAR(date(2022,1,1),TODAY())
问题:创建一个度量,显示本年度的累计总金额,即使日期切片器从前一年开始:
以下是我的代码:
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结果
英文:
Here is the test data:
Here is the date Data:
Dates = CALENDAR(date(2022,1,1),TODAY())
Relationship between the tables:
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
Test Scenario 2: Does Not Work, Shows 40 instead of 130
Test Scenario 3: Does not work, Even when the year is the current year
答案1
得分: 1
替换 AllSelected 为 All,因为 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])) )
Simply you can use the following formula for the YTD.
YTD = CALCULATE(SUM(test[Totals]), YEAR(test[Date]) = YEAR(TODAY()), REMOVEFILTERS(Dates[Date]))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论