如果值介于两个日期之间，则 DAX JOIN [英] DAX JOIN if a value is between two dates

问题描述

在表 A 中，我有一个名为销售日期的日期字段.在表 B 中，我有开始日期、结束日期和财政季度.我想要一个新表，其中包含表 A 中的销售日期和表 B 中的财政季度.我将如何在 DAX 中执行此操作?由于 PowerBI 中的大多数 UI 只允许相等.

In Table A I have a date field called Sales Date. In Table B I have start date, end date, and fiscal quarter. I would like a new Table with the Sales Date from Table A and the fiscal quarter from Table B. How would I do that in DAX? Since most of the UI in PowerBI only allows equality.

推荐答案

如果你想要它作为一个新表，你可以这样做:

If you want it as a new table you can do something like this:

NewTable = SUMMARIZECOLUMNS(
               TableA[SalesDate],
               "FiscalQuarter",
               CALCULATE(
                   MAX(TableB[FiscalQuarter]),
                   TableB[StartDate] <= VALUES(TableA[SalesDate]),
                   TableB[EndDate] >= VALUES(TableA[SalesDate])))

您也可以在 TableA 上添加 FiscalQuarter 作为计算列:

You could also just add FiscalQuarter as a calculated column on TableA:

FiscalQuarter = CALCULATE(
                    MAX(TableB[FiscalQuarter]),
                    FILTER(TableB,
                        TableB[StartDate] <= TableA[SalesDate] &&
                        TableB[EndDate] >= TableA[SalesDate]))

这篇关于如果值介于两个日期之间，则 DAX JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！