Forum Discussion
Dynamic date substitution
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.
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
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.
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 AzamThnks, 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