topic Re: Dynamic date substitution in DAX Commands and Tips

Dynamic date substitution

dimotori — Fri, 13 Jun 2025 16:03:44 GMT

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

Re: Dynamic date substitution

Nasif_Azam — Fri, 13 Jun 2025 17:27:39 GMT

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

Re: Dynamic date substitution

V-yubandi-msft — Mon, 16 Jun 2025 12:36:15 GMT

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.

Re: Dynamic date substitution

V-yubandi-msft — Thu, 19 Jun 2025 12:26:40 GMT

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.

Re: Dynamic date substitution

dimotori — Thu, 19 Jun 2025 21:48:01 GMT

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.

Re: Dynamic date substitution

Nasif_Azam — Fri, 20 Jun 2025 06:08:25 GMT

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:

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.
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

Re: Dynamic date substitution

V-yubandi-msft — Fri, 20 Jun 2025 07:37:14 GMT

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.

Re: Dynamic date substitution

dimotori — Fri, 20 Jun 2025 21:28:37 GMT

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

Re: Dynamic date substitution

V-yubandi-msft — Mon, 23 Jun 2025 11:10:51 GMT

Hi dimotori ,

I tested this scenario with sample data, and the logic works as expected. When a confirmed delivery date is available, the corresponding amount is displayed otherwise, it defaults to the requested delivery date.

I validated the results using a table visual both the placement of values and the total amounts are accurate and meet the requirements.

FYI:

I’ve attached the .pbix file for you to review the DAX logic and model relationships. Let me know if you'd like to see this in a matrix visual or explore any enhancements.

Regards,

Yugandhar.

Re: Dynamic date substitution

V-yubandi-msft — Thu, 26 Jun 2025 13:10:33 GMT

Hi dimotori ,

Does the solution provided address your issue, or are you still experiencing the problem? Your feedback is important to the community. If your issue has been resolved, please mark the response as Accepted Solution to help others find it more easily.

Thank you.

Re: Dynamic date substitution

V-yubandi-msft — Mon, 30 Jun 2025 16:57:58 GMT

Hi dimotori ,

I hope my message clarified everything. If everything is resolved, please mark it as Accepted solution. Please let me know if you are facing any issues.

Thank You.

Re: Dynamic date substitution

dimotori — Mon, 30 Jun 2025 22:08:04 GMT

Hi, thank you for looking into this as well, however in the proposed solution date is not populating neither.

same as in your screenshot, confirmed date for order A001 is June 25, while in matrix table it pulls June 24, as it is in "Order table".

Besides, proposed LOOKUPVALUE function will not work well in Direct Query mode i have for Confirmed date table

Re: Dynamic date substitution

Anonymous — Wed, 09 Jul 2025 11:56:02 GMT

Hi dimotori ,
Thank you for the follow-up. We are happy to assist you!
Please refer the attached screenshot and file for your reference.

I hope this resolve your query.
Thank you.

Re: Dynamic date substitution

dimotori — Wed, 09 Jul 2025 15:00:27 GMT

Thanks, v-pagayam-msft,

unfortunately calculated column will not work in direct query mode, needs to be a measure.

thanks for effort, though

Preview

Re: Dynamic date substitution

V-yubandi-msft — Thu, 10 Jul 2025 07:24:29 GMT

Hi dimotori ,

I’ve included the measure as you recommended and attached the file. Please review it when you have a chance and let me know if everything is correct.

Hope this is helpful.