Measure over 2 dates with a many to many relationship

[Khomotjo]

Hello Everyone, I am building a dashboard from  a database. I have 2 date fileds: required date and finalised date. I have measures in my table that were calculated based on required date ( such as open orders on the day/late orders on the day). I want to add a coloumn to this table for all orders finalised on the day.  The issue is that not all orders finalised on a particular  date were required on the date. Sometimes the we work ahead or catchup on orders. I thought of doing a loop over finalised date and counting all entries that match a specific date but not sure how to add this back as a coloumn to the table I have created for other KPIs using required date. Thanks,

[Khomotjo]

Thanks @bhanu_gautam  it worked perfectly. Quick question :  Does it matter if I use a calculated coloumn or a measure? Which one is best practise or is it a matter  only of preference?

[bhanu_gautam]

Calculated columns will take space and measure are quick, calculated columns are used for row iteration

[bhanu_gautam · Accepted solution]

@Khomotjo You can try using create a calculated column in your table that counts the number of orders finalized on each day

DAX
FinalizedOrdersOnDay =
CALCULATE(
COUNTROWS(OrdersTable),
FILTER(OrdersTable, OrdersTable[FinalisedDate] = EARLIER(OrdersTable[RequiredDate]))
)

If you prefer to create a measure instead of a calculated column, you can use the following DAX formula:

DAX
FinalizedOrdersOnDayMeasure =
CALCULATE(
COUNTROWS(OrdersTable),
FILTER(ALL(OrdersTable), OrdersTable[FinalisedDate] = MAX(OrdersTable[RequiredDate]))
)

Once you have created the measure, you can add it to your table visual in Power BI. This will display the count of orders finalized on each required date alongside your other KPIs.