Dynamic date substitution

[dimotori]

Hi, implementing write back with PowerApps have two tables one with order number, amount and requested delivery dates (table 1) another with order number and confirmed delivery date (table 2).  Table1 is in import mode, Table2 is direct query mode (data is being populated through PowerApp).

both tables are connected to the same calendar table

Trying to write a measure that would "use" date in table 2 if available for a delivery amount from table 1 for a matrix visual in PBI.

any successful experience implementing something similar 

[V-yubandi-msft]

Hi @dimotori ,

Thanks for sharing, @Nasif_Azam the updated solution using TREATAS is an effective workaround it bypasses cross table filtering limitations by matching OrderNumber values between the two tables. This enables Power BI to evaluate the ConfirmedDeliveryDate from Table2 when available, and fall back to the RequestedDeliveryDate from Table1 when necessary.

I'd recommend testing this approach in your matrix visual with Calendar[Date] on the rows and the dynamic delivery amount measure in the values section. Let me know how it behaves in your report

Appreciate your continued contributions to the community @Nasif_Azam .

Best regards,

Yugandhar.

[V-yubandi-msft]

Hi @dimotori ,

Have you had a chance to check the response shared by @Nasif_Azam ? Please let us know if it worked for you.
If your issue is resolved, kindly mark it as the accepted solution it helps others in the community who may face a similar issue.

Thank You.

[V-yubandi-msft]

Hi @dimotori ,

Thank you for reaching out to the Fabric Community.

The solution provided by the @Nasif_Azam , is well thought out and suits your scenario. Please give it a try, and if you need any further clarification or assistance, we are here to help.

A special thanks to @Nasif_Azam  , for the quick and insightful response.

Regards,

Yugandhar.

[Nasif_Azam]

Hey @dimotori ,

Sure! Here's a completely rewritten, original explanation tailored to your use case. It avoids any direct reuse of phrasing and presents the solution clearly.

Overview

You're working with two tables in Power BI:

• Table1 (Import mode): Contains OrderNumber, Amount, and RequestedDeliveryDate.

• Table2 (DirectQuery mode): Contains OrderNumber and ConfirmedDeliveryDate (updated through PowerApps).

• Calendar Table: Related to both date fields to support date-based analysis.

Your goal is to show delivery amounts from Table1, grouped by ConfirmedDeliveryDate from Table2 when it exists. If it doesn't, use the RequestedDeliveryDate from Table1 all within a matrix visual that’s based on your calendar table.

1. Optional Column for Confirmed Dates (if you prefer lookup logic)

If you want to bring in the confirmed delivery date into Table1:

ConfirmedDate = 
LOOKUPVALUE(
    Table2[ConfirmedDeliveryDate],
    Table2[OrderNumber], Table1[OrderNumber]
)

2. Create a Custom Measure for the Grouping Date

This measure decides whether to group by ConfirmedDeliveryDate or fall back to RequestedDeliveryDate:

EffectiveDeliveryDate = 
VAR Confirmed =
    CALCULATE(
        MAX(Table2[ConfirmedDeliveryDate]),
        Table2[OrderNumber] = MAX(Table1[OrderNumber])
    )
RETURN
    IF(NOT ISBLANK(Confirmed), Confirmed, MAX(Table1[RequestedDeliveryDate]))

This gives you a dynamic date based on the availability of confirmed dates.

3. Measure to Display the Amounts

To show the correct delivery amount on the matrix based on the selected date from the calendar table:

DynamicDeliveryAmount = 
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
    CALCULATE(
        SUM(Table1[Amount]),
        FILTER(
            Table1,
            VAR Confirmed =
                CALCULATE(
                    MAX(Table2[ConfirmedDeliveryDate]),
                    Table2[OrderNumber] = Table1[OrderNumber]
                )
            VAR ChosenDate =
                IF(NOT ISBLANK(Confirmed), Confirmed, Table1[RequestedDeliveryDate])
            RETURN ChosenDate = CurrentDate
        )
    )

Use this measure in your matrix with the Calendar[Date] as the row or column field. It ensures the amount is shown for the correct date, whether confirmed or requested.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[dimotori]

Nasif, thank you for taking a look, I tried similar solutions before but with little success. replicated #3 proposed by you below, does not seem to work

both Table 1 and Table 2 are connected to Calendar as 1:* (noticed that filter expression for Table 2 refers to Table 1 which dax would not allow), tried both MAX and SELECTEDVALE for that, with no result. 

[Nasif_Azam]

Hey @dimotori ,

It seems that the filter expression you're using in the measure might be causing issues due to the relationship and how the tables are connected. Since you're using a 1:* relationship between Table1 (import mode) and Table2 (direct query mode), this could lead to problems when trying to reference fields from Table2 in the filter of Table1. Updated approach you can try:

1. Ensure Correct Relationship: Make sure that the relationship between your tables is properly set. It should be based on OrderNumber for both tables. If your ConfirmedDeliveryDate is in Table2 and RequestedDeliveryDate is in Table1, you should be able to perform the check in your measure correctly.

2. Update the Measure to Avoid Direct Filtering:
   Instead of using a FILTER directly on Table1 inside your measure, you can make use of TREATAS to pass the filter from the calendar table to Table2, ensuring you're using the correct date context.

DynamicDeliveryAmount = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR Confirmed =
    CALCULATE(
        MAX(Table2[ConfirmedDeliveryDate]),
        TREATAS(
            VALUES(Table1[OrderNumber]),
            Table2[OrderNumber]
        )
    )
VAR ChosenDate =
    IF(NOT ISBLANK(Confirmed), Confirmed, MAX(Table1[RequestedDeliveryDate]))
RETURN
    CALCULATE(
        SUM(Table1[Amount]),
        FILTER(
            Table1,
            ChosenDate = CurrentDate
        )
    )

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[dimotori]

Thnks, reviewed that again, but seem to get the same result:

Note: there are no relationships between Table 1 and Table 2, as that would not allow to create any between them and calendar at the same time