2 tables merged in a new one: how to filter it using a measure from a third table?

[DaniloTavecchio]

Hi all,

Situation: I have a business table, 'ATRSHRP', updated daily that contains rows with a transaction date in it and I have my 'CALENDAR' Table (from 1/1/2023 up to 31/12/2029).

Requirement: I want to know if in this business table there are some dates with no transactions at all: it is an error I have to detect.

My Solution: I have merged the calendar table and the business table so that I keep all the dates present in the CALENDAR but (excluding) the dates that has rows in the ATRSHRP. 

PS Maybe this is not the easiest solution and the Calendar table is not really needed, any different solution is more then welcomed

= Table.NestedJoin(Calendar, {"Date"}, ATRSHRP, {"Data Elaborazione check"}, "ATRSHRP", JoinKind.LeftAnti)

Unfortunately the merged table 'DATI MANCANTI' will show me also the dates in the future or in the past; in other words is showing me dates before the minumum date and after the maximum date of 'ATRSHRP'. I have built two measures:

MaxDateCheck and MinDateCheck but I do not know how to use them to filter 'DATE MANCANTI'

thanks in advance

[AnalyticPulse · Accepted solution]

hello @DaniloTavecchio 

Try below dax and let me know if it works:
DATI_MANCANTI =
VAR MinDateCheck = CALCULATE(MIN('ATRSHRP'[Data Elaborazione check]))
VAR MaxDateCheck = CALCULATE(MAX('ATRSHRP'[Data Elaborazione check]))
RETURN
FILTER(
CALENDAR,
CALENDAR[Date] >= MinDateCheck &&
CALENDAR[Date] <= MaxDateCheck &&
NOT CALENDAR[Date] IN
SELECTCOLUMNS('ATRSHRP', "Date", 'ATRSHRP'[Data Elaborazione check])
)

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

Commercial Real Estate Portfolio Overview by DataFreedom

Superstore Sales 2022 vs 2023: Year-on-Year Performance Comparison dashboard

Case Study: PowerBI Dashboard Developer for Sales Visualization

Case Study: Banking and Finance dashboard (Step by step guide)

Powerbi Visualisation

[DaniloTavecchio · Accepted solution]

Thanks a lot, it works perfecty !!!

Danilo

[AnalyticPulse · Accepted solution]

hello @DaniloTavecchio 

Try below dax and let me know if it works:
DATI_MANCANTI =
VAR MinDateCheck = CALCULATE(MIN('ATRSHRP'[Data Elaborazione check]))
VAR MaxDateCheck = CALCULATE(MAX('ATRSHRP'[Data Elaborazione check]))
RETURN
FILTER(
CALENDAR,
CALENDAR[Date] >= MinDateCheck &&
CALENDAR[Date] <= MaxDateCheck &&
NOT CALENDAR[Date] IN
SELECTCOLUMNS('ATRSHRP', "Date", 'ATRSHRP'[Data Elaborazione check])
)

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

Commercial Real Estate Portfolio Overview by DataFreedom

Superstore Sales 2022 vs 2023: Year-on-Year Performance Comparison dashboard

Case Study: PowerBI Dashboard Developer for Sales Visualization

Case Study: Banking and Finance dashboard (Step by step guide)

Powerbi Visualisation