Forum Discussion
Date table new column lookup
Hello dear community,
I have an autogenerated Date Table ( CALENDARAUTO ) with column DatesWithPeriod that indicates whether the date is included in period of transactions:
And there is an another "Transaction" table with "Period Start Date".
Task: to create a new column in date table ("Date is a Start Period") that will indicate if this value is in transaction table in column Period Start Date.
The result should be like this:
Thank you in advance for your help!
Hi Delphia ,
T1 = Date Table
T2 = Transaction Table
If you need column:
Date is a Start Period = VAR _countDays = COUNTAX ( FILTER ( T2, T2[Date] = T1[Date] ), T2[Date] ) RETURN IF ( ISBLANK ( _countDays ), FALSE (), TRUE () )If you need measure:
#Date is a Start Period = VAR _currentDay = SELECTEDVALUE ( T1[Date] ) VAR _countDays = COUNTAX ( FILTER ( T2, T2[Date] = _currentDay ), T2[Date] ) RETURN IF ( ISBLANK ( _countDays ), FALSE (), TRUE () )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
4 Replies
Hi Delphia ,
T1 = Date Table
T2 = Transaction Table
If you need column:
Date is a Start Period = VAR _countDays = COUNTAX ( FILTER ( T2, T2[Date] = T1[Date] ), T2[Date] ) RETURN IF ( ISBLANK ( _countDays ), FALSE (), TRUE () )If you need measure:
#Date is a Start Period = VAR _currentDay = SELECTEDVALUE ( T1[Date] ) VAR _countDays = COUNTAX ( FILTER ( T2, T2[Date] = _currentDay ), T2[Date] ) RETURN IF ( ISBLANK ( _countDays ), FALSE (), TRUE () )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Thank you so much! It works perfectly!
Delphia , Try a new column like
new column in date tbale =
var _cnt =count(filter(Transaction, Transaction[Date] = Date[date]), Transaction[Date] )
return
if(isblank(_cnt),false(), true())Thank you, but it doesn't work for me.
Power BI doesn't allow to use the column [Period Start Date], please look at this screenshot.
I'm not sure if it can help, but the column [Period Start Date] could contain duplicated values as there could be several transactions in the same day. Like this (2019-06-30):
Thank you!
