Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Create a Calculated Column from Different Tables

Hi,

 

I have data as in the table below:

I need period end dates (month end dates from jan to dec 2018). I was able to create a new table with the required dates. However for my further calculations, I need to match my live date in existing data to calculated period end date.

 

For example, if I need to know who my new client for a particular month is, so I will use the logic:

=If(Table1[livedate] = table2[period end date], “new”)

 

ISSUE: The formula bar doesn’t let me use table 2 data and I am unable to create any further column or measure. I have created the relationships between 2 feilds and still isnt working (This can easily be done in Tableau by creating a parameter). Can someone please help!.

 

Thanks in advance.

 

Today

Live date

Client ID

30-Oct

01-Sep

A00034

30-Oct

26-Oct

12303

30-Oct

26-Oct

12302

30-Oct

25-Oct

564735

30-Oct

01-Sep

102538

30-Oct

01-Sep

109928

30-Oct

01-Sep

0928abc

30-Oct

26-Oct

1856XY

 

10 REPLIES 10
Stachu
Community Champion
Community Champion

if there is a join between the tables (e.g. 1:1, 1:many) you can reference them with:
RELATED when referring to the 1

RELATEDTABLE when referring to the many



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

 

TodayLive dateClient IDActual statusStatus required
26-Oct30-SepA00034newnew
26-Oct26-Oct12303oldnew
26-Oct05-Oct12302oldnew
26-Oct28-Feb564735newnew
26-Oct01-Sep102538oldnew
26-Oct15-Sep109928oldnew
26-Oct31-Jul0928abcnewnew
26-Oct26-Oct1856XYoldnew

Hi @Anonymous,

 

Could you please explain the logic of the output? Wht the Status required are all new?

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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 Clients30
Lost Clients12
Opening balance20000
Closing Balance65000

 

The data changes to this, when september is selected. An so on.

 

New Clients10
Lost Clients5
Opening balance8000
Closing Balance45000

 

All fields here are calculated fields. How do i categorise my data in power BI? (Can easily do in excel and Tableau)

Hi @Anonymous,

 

Kindly share your pbix. You can upload the file to dropbox and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Stachu
Community Champion
Community Champion

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 problem

 

If 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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

Stachu
Community Champion
Community Champion

I still don't get the  logic, it looks inconsistent to me

you write:

All fields are related to period end dates and live dates for the client. Eg:

New clients = if(Live date = period end date, new)

but in the table you posted:

TodayLive dateClient IDActual statusStatus required
26-Oct30-SepA00034newnew
26-Oct26-Oct12303oldnew

according to that table for the customer 12303 the status should be new, even though 26-Oct is not in an end date (31 Oct is)

do you mean that when October is selected the flag should say "new" because client was added in the month of October?

so does 'end date' mean full month?

 

Opening balance = if(Live date = (period end date -1), balance)

-1 means one month, not one day, right?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.