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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lavina03
Helper I
Helper I

How to Sync the dates from 2 different sources

HI,

 

I need help on or possible way for my below scenario

 

I have data coming from 2 different sources

 

Source A shows

- Resource booked on daily bases

 

Source B shows

- Resource log hours on daily bases

 

I want to know how I can create a stats resource booked v/s resource logged hours by monthly

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Yep, as along as your date types are date or date/time, it should work fine.  Note *this* is the mode you are looking for... 

model.png

View solution in original post

13 REPLIES 13
JuanBolas
Helper II
Helper II

@Eric_Zhang thank you!  I had my relationships wrong.

 

BTW, do you have any idea of why the subtotals measure is not working correctly? In some instances it adds something that doesn't appear in the columns.

 

Thanks in advance.

ankitpatira
Community Champion
Community Champion

@lavina03 what you want to do is create a blank table in power bi desktop then use merge queries option and merge two data sources queries in third table. this will give you third query with all columns from both including dates. then simply use resource booked and resource log hours on any visual and choose any date field on x-axis. provided you have time intelligence on in power bi desktop (by default it is) you will have data by weekly, monthly and quarterly. you can also use slicer to see only for monthly.

Hi @ankitpatira,

 

Let me explain you my query:

 

I already have data coming from 2 different sources:

 

1) First Data is coming from API wherein I have 3 tables [resources], [projects], [resource booked]

 

2) Second data is coming from db wherein I have 3 tables [resources], [projects], [resource log hours]

 

The data from this 2 sources doesn't have any relation between them other then [resource email id]

 

3) So I created master user table to get common users from both these sources

 

4) Then I passed ID to projects tables so that they have common ID

 

5) Now the problem I am facing is I want to show stats based on projects wise say like 

 

Project 1 for the month of June [resources booked v/s resource logged hours]

 

 


@lavina03 wrote:

Hi @ankitpatira,

 

Let me explain you my query:

 

I already have data coming from 2 different sources:

 

1) First Data is coming from API wherein I have 3 tables [resources], [projects], [resource booked]

 

2) Second data is coming from db wherein I have 3 tables [resources], [projects], [resource log hours]

 

The data from this 2 sources doesn't have any relation between them other then [resource email id]

 

3) So I created master user table to get common users from both these sources

 

4) Then I passed ID to projects tables so that they have common ID

 

5) Now the problem I am facing is I want to show stats based on projects wise say like 

 

Project 1 for the month of June [resources booked v/s resource logged hours]

 

 


@lavina03

 

Your requriement is not clear. We have no idea on what the tables are like and what relationship among them. Please give some sample data and expected output for further advice.

 

@Eric_Zhang,

 

I know the requirements what I have provided is incomplete. I have figured out the issue and the dates are missing in my data.

 

Can you please provide me some solution or formula on how to compare or get the missing dates for the following data.

 

https://drive.google.com/file/d/0Bw-NoGKVJq_nMDRIQzMtVi03Nkk/view?usp=sharing

@lavina03

 

To find out the missing dates, please follow the below steps.

  1. Create a calendar table.
    DimDate = CALENDAR(MIN(sample_data[day]),MAX(sample_data[day]))
  2. Create a calculated table.
    Missing Dates = FILTER(DimDate,ISBLANK(LOOKUPVALUE(sample_data[day],sample_data[day],DimDate[Date])))

Capture.PNG

@Eric_Zhang,

 

Did you find any solution for my queries

hi @lavina

You make sure that your dates column are in date format. After this the calendar auto will work.



Lima - Peru
Anonymous
Not applicable

Yep, as along as your date types are date or date/time, it should work fine.  Note *this* is the mode you are looking for... 

model.png

Anonymous
Not applicable

On the modeling tab of PBI desktop, you can click the "New Table" button, and use Calendar = CALENDARAUTO()... which should magically work to give you a new calendar table (which has the required... exactly 1 row for every day).

 

(Bunches of other ways to create calendar tables, but .... you need one).   Then just create your relationships and ... generally use your calendar table for most anything involving dates.

@Anonymous,

 

I tried to create a new table using CALENDARAUTO() function but unfortunately it is not allowing to make relation between these 2 data:

 

https://drive.google.com/file/d/0Bw-NoGKVJq_nSEl0bFpQdnNRc0k/view?usp=sharing

https://drive.google.com/file/d/0Bw-NoGKVJq_nMDRIQzMtVi03Nkk/view?usp=sharing

 

Can you try from your side

Anonymous
Not applicable

you should build a separate calendar table that has all dates, then create relationships to your other date related columns.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.