如何构建DAX度量来计算特定月份的YTD值之和?
在这里,我们将FactTable按月分组 . FactTable充满了实际数据和预测数据 . 知道何时实际结束的唯一方法是[年末截止日期]列[年末截止日期]中的信息 . 在[年终截止日期]列[年末截止日期]中 - 它是一个单值表 - 我们有一个有趣的选择月份,我们希望看到YTD的计算 . 在我们的例子中,它是三月 . FactTable每月不定期更新,通常延迟一个月 . 由于不规则的更新,无法将其链接到像TODAY这样的时间函数 .
我们希望在黄色Card Visual中显示正确的YTD值[月份结束] . 当我们点击"2018-03"上的切片器时,我们几乎得到了我们想要的东西 - 在黄卡中正确值为66 . 但是这种解决方案不是自动的 . 我想在[年末]月份变化时自动查看正确的值,在我们的情况下是4月或5月 . 我不希望用户完成它 .
我的绝望努力可以从文件中下载:DAX YTD.pbix
我以各种方式追赶鹿:
-
在DAX度量中使用 FILTER 函数 . 但似乎FILTER功能是苛刻的 . 它首先应用于事实表,仅选择一个月,然后错误地计算YTD值 . 所以,如果 forcing order of calculation and filtering 有任何选择,那就有希望了 .
-
我尝试 SWITCH 函数显示特定月份的正确结果,0或其他月份为null . 虽然我在这方面取得了成功,但我无法利用它 . 在过滤方面,我和以前一样无望 . 顺便说一句,如果SWITCH在表格末尾产生总数,我就能做到,但事实并非如此 . 出奇 .
-
我希望在 RELATED 函数中能够在[Cut off date]表中显示正确的结果 . 到目前为止,我还没有走出迷雾 .
我很感激你的帮助 .
Update before bounty. 上升到更高水平 . 我已经向FactTable引入了一个Category列 . 请下载DAX YTD by category.pbix . 所以过滤现在变得更加复杂 . 我想为苹果类别提供正确的YTD数据 .
3 回答
您是否使用了
Calendar
表中的Date
列,而不是FactTable
中的列?如果使用
FactTable
中的日期列,则在日期应用过滤器时,它将过滤3月份的事实记录,然后进行计算,结果为33
.如果您使用
Calendar
中的那个,当您对其应用过滤器时,它会过滤Calendar
上的记录(您希望在图表中显示),因此基础计算仍将保持不变 .一个工作的例子:
Calendar = CALENDAR(DATE(2010, 1, 1), DATE(2020, 12, 31))
我建议你改变措施的计算,以避免在某些情况下缺失值:
更新:
对我来说,你现在想要实现的目标更加清晰,但对我来说这似乎仍然是个好消息 .
我知道您希望按原样显示仪表板,这样用户每次都不需要单击/输入以查看它们应该看到的内容 . 那个's why I don't为什么你需要创建一个新的表来存储
Cut off date (End of YTD)
. 如何自动维护?上面的
relative date filtering
解决方案实际上仍然适用于您共享的.pbix文件 . 如果将Date
列从Calendar
表拖到黄牌的可视级别过滤器并添加相对日期过滤,则应如下所示:对于
End of YTD
visual,您可以使用following measure获取上个日历月的第一天,因此您无需为其创建另一个表:希望这是你想要实现的目标:
Updated file供您参考 .
再次更新:
我认为你必须编写自己的
YTD
计算而不是使用内置计算,这样你就可以利用你在另一个表中定义的截止日期 . 在这里,我假设你在'Cut off date'[End of YTD]
中只有一行 . 请注意,我已将ALL()
添加到过滤器中,以便黄色卡保持不变(66),而不是在单击其他行/过滤器时显示空白:我会通过在Calendar表中添加一个计算列来将每行分类为“YTD”或“其他”来解决此问题 .
然后我会添加新的 Is YTD 字段到您的Card visual的Visual level过滤器,然后从Basic过滤列表中选择 YTD . 显示的度量可以是简单的 Total 度量:SUM(FactTable [Value]) .
这是一个比任何特定的测量体操更灵活和可修复的解决方案 . 您不需要采取大量措施来在每个基础测量之上应用所需的逻辑 - 它们都将自然地工作 . 您可以在任何级别应用过滤器:Visual,Page,Report,或将其放在切片器中以供最终用户控制 .
我更喜欢返回文字结果,例如“YTD”/“其他”(而不是1/0,真/假或是/否),因为这允许容易地扩展到其他要求,例如“YTD之前”(2017年1月1日至2017年3月1日) . 在视觉效果中使用时也更清晰 .
实际上,我不应该声称这个设计的功劳 - 这大致遵循了Cognos Transformer的相对时间功能在90年代的功能 .
我在我的期刊/年初至今报告(最后一页)中做了类似的事情:http://ciprianbusila.ro/
我使用了所选月份的索引值(范围1-12)并基于此我使用max函数创建了一个度量,请参阅下面的代码: