Forum Discussion
Create a Calculated Column from Different Tables
Hi Stachu,
I'll rephrase my statement.
I need to match year and month of live date and period end date. I am using the following formula (using related function) but it is not populating any data.
Column = IF(AND(YEAR(Table[LiveDate]) = YEAR(RELATED('Table2'[PeriodEndDate]), MONTH(Table[LiveDate]) = MONTH(RELATED('Table2'[PeriodEndDate], "New")
Can't find the error
you're missing few closing brackets and false value for IF, this should work
Column =
IF (
AND (
YEAR ( Table[LiveDate] ) = YEAR ( RELATED ( 'Table2'[PeriodEndDate] ) ),
MONTH ( Table[LiveDate] ) = MONTH ( RELATED ( 'Table2'[PeriodEndDate] ) )
),
"New",
"Old"
)if the code above doesn't work:
1) are both Table[LiveDate] and Table2[PeriodEndDate] of type date?
2) is there a relationship between the 2 tables?
3) can you share the sample of Table2 data?
Hi Stachu,
this formula isn't working either. table 2 is a created table with following data:
The formula used is: Table 2 = FILTER(CALENDAR("01-01-2018",TODAY()),[Date]=EOMONTH([Date],0))
Date 31 January 2018 28 February 2018 31 March 2018 30 April 2018 31 May 2018 30 June 2018 31 July 2018 31 August 2018 30 September 2018 Using the formula you mentioned i am only getting new against the dates where live date is end of month. However what i need is this (the client live date of 26th oct is also a new client for october 2018)
Table 1
Today Live date Client ID Actual status Status required 26-Oct 30-Sep A00034 new new 26-Oct 26-Oct 12303 old new 26-Oct 05-Oct 12302 old new 26-Oct 28-Feb 564735 new new 26-Oct 01-Sep 102538 old new 26-Oct 15-Sep 109928 old new 26-Oct 31-Jul 0928abc new new 26-Oct 26-Oct 1856XY old new based on what you've written I'm not sure whether I get the requirement properly
are the values in the column supposed to chagne depending on the month you select? if that's the case then it's not possible.
you could show the customers which are new for a given month with a measure, but it's a different problemIf the values in the column are static - do you mean to compare them with the real time date? e.g. using TODAY()? if that's the case then there is no need for the additional table
Hi Stachu
Apologies for too many complications.
I have to create something similar to date parameter in Tableau. I need to report the client status at the end of every month. This includes opening and closing balances, new and lost clients etc.
All fields are related to period end dates and live dates for the client. Eg:
New clients = if(Live date = period end date, new)
Opening balance = if(Live date = (period end date -1), balance)
Closing balance = balance as on period end date etc.
I’ll add a slicer in the end for users to see data for previous months and it should filter data specifically for that month.
Steps I followed
1. Created a new table with period end dates (that will remain static)
2. Added another column for month and year of period end date
3. Ceated a relationship to join month and year of period end date to month and year of live date
Now, based on these steps, the data is not getting populated.
Can you tell me a better and easy way to get right data.
Thanks
Hi Anonymous,
Could you please explain the logic of the output? Wht the Status required are all new?
Regards,
Frank
I need to categorise my data in differnt months. I would add a filter for period end date on my dashboard. So if my users want to take a look at the status at the end of october, they would select october and they would have filtered data available just for october.
For example this is what my dashboard looks like when october month is selected:
New Clients 30 Lost Clients 12 Opening balance 20000 Closing Balance 65000 The data changes to this, when september is selected. An so on.
New Clients 10 Lost Clients 5 Opening balance 8000 Closing Balance 45000 All fields here are calculated fields. How do i categorise my data in power BI? (Can easily do in excel and Tableau)
