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])))

您也可以将 FiscalQuarter 添加为 TableA ：

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

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