Forum Discussion
ericOnline
Post Patron
Post PatronDetermine MIN date across tables using DAX
I come from the PowerApps world, just getting familiar with DAX and PowerQuery. In PowerApps, to determine the minimum value of a set, I'd write something like: MIN(value1, value2, value3, etc.) ...
ericOnlinePost Patron
Post PatronHi Anonymous ,
I was able to translate your solution to my use case. Since I am generating a Date Table based on timestamps in 3 tables, rather than a Measure, here is what I used: (Thank you!)
DATE_TABLE =
VAR MIN_TS =
IF(
MIN(TABLE1[TIMESTAMP]) <=
MIN(TABLE2[TIMESTAMP]),
MIN(TABLE1[TIMESTAMP]),
IF(
MIN(TABLE2[TIMESTAMP]) <=
MIN(TABLE3[TIMESTAMP]),
MIN(TABLE2[TIMESTAMP]),
MIN(TABLE3[TIMESTAMP])
)
)
VAR MAX_TS =
IF(
MAX(TABLE1[TIMESTAMP]) >=
MAX(TABLE2[TIMESTAMP]),
MAX(TABLE1[TIMESTAMP]),
IF(
MAX(TABLE2[TIMESTAMP]) >=
MAX(TABLE3[TIMESTAMP]),
MAX(TABLE2[TIMESTAMP]),
MAX(TABLE3[TIMESTAMP])
)
)
RETURN
CALENDAR(
MIN_TS,
MAX_TS
)Sure would be more intuitive if DAX allowed something like:
DATE_TABLE =
CALENDAR(
MIN(TABLE1[TIMESTAMP], TABLE2[TIMESTAMP], TABLE3[TIMESTAMP]),
MAX(TABLE1[TIMESTAMP], TABLE2[TIMESTAMP], TABLE3[TIMESTAMP])
)Oh well! Job security!
CALENDARAUTO() would also give you the result by scanning all tables in your data set with dates and providing the correct range, you can also change the starting month number - for example July with CALENDARAUTO(6)