PBI One measure grouped by multiple date columns

DM-BUSA 0
Viewed 21

For my matrix I'd like to have one column on the left with a date then I have a fact table with quantities and multiple date columns. Think of it as order date and date received, date billed etc.

YY-MM e.g. JAN | Tot. Qty grp Order Date | Tot Qty grp Date Recv | Tot Qty Date Billed

YY-MM e.g. Feb | Tot. Qty grouped Order Date | Tot Qty grp Date Recv | Tot Qty Date Billed

Totals

Am I going to have to split these into a few different tables then combine them or can I create group by calculations but have a single calendar on the left column? Everything I am searching for seems to not have the multiple date context I am looking for. Thanks in advance.

powerbi
1 Answers

Without seeing your data and assuming data is normalized, and as Mik said in the above comment, you may need to use the "inactive relationship" feature, basically you would need to create as many relationships as you need between your LEFT table (the one with the date) and your RIGHT table (the one with multiple dates).

You will notice that Power BI will not allow you to have multiple "active" relationships, you just need to uncheck the box "Make this relationship active" when creating the relationship itself.

After you create all your "inactive" relationships between your two tables, you need then use the USERELATIONSHIP() and CALCULATE() DAX expressions to calculate your metrics. One example would be:

Tot. Qty grp Order Date =
CALCULATE(
    COUNTROWS(Qty)
    ,USERELATIONSHIP(Left_Table[Date], Right_Table[OrderDate])
)

You can refer to this Microsoft official documentation to learn more about Inactive relationships and how to implement USERELATIONSHIP() and CALCULATE() DAX

luamar0
Related