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
Katerina_Bis
Frequent Visitor

ytd

Hello,

 

In power BI desktop my problems is that I need to combine 2 different YTD measures (calculated on 2 tables, thus 2 different date tables used too).

The program does not allow me to include 2 YTD in one report – it shows either one or another, but not both at the same time.

Basically I need to show in one table per client, both: YTD of sales figures and YTD of time spent figures, where "sales figures" come from SALES table and "time spent figures" come from TIME_SPENT table.

Also I need to calculate the ratio YTD_sales/YTD_time_spent.

To determin the time frame I use YEAR and MONTH slicers.

Table eaxmple:

Client       YTD_sales           YTD_time_spent        YTD_sales/YTD_time_spent 

Client1      1000                         50                                  20

Client2       200                          20                                  10

etc.

 

Thank you in advance.

Katerina

1 ACCEPTED SOLUTION

Hi @Katerina_Bis,

 

When you added measures *YTD_turnover amd *YTD_timelog to a table visual, which error threw out? Please try to set the cross filtering direction to Both for each relationship among those tables. For more information, see: Create and manage relationships in Power BI Desktop.

 

Best Regards,
QiuyunYu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
tringuyenminh92
Memorable Member
Memorable Member

Hi @Katerina_Bis,

 

I got your point but could you please share your sample data or data structure? so i could quickly figure out solution.

Hello,

 

I cannot share the data sample. But I have drafted the data structure and the desirable result.

Hope it helps.

data_structuredata_structure

Hi @Katerina_Bis,

 

When you added measures *YTD_turnover amd *YTD_timelog to a table visual, which error threw out? Please try to set the cross filtering direction to Both for each relationship among those tables. For more information, see: Create and manage relationships in Power BI Desktop.

 

Best Regards,
QiuyunYu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hello @v-qiuyu-msft

 

I have changed the relationship between clients tables from "Both" to "Single" for Cross filter direction and it solved my problem.

thank you for the usefull link to the theory material.

 

Kind regards,

Katerina

Hi @Katerina_Bis,

 

It seems the issue was solved. If that is a case, would you please mark a help reply as an answer so that we can close the thread?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Katerina_Bis,

 

I think you should have one more Dates table for both of Finance_data and JIRA_Data as side-by-side topic and you could use date column of that Dates table for slicer.(Finance and JIRA will refer date column of new DATES table)

One more concern that *YTD_turnover and *YTD_timelog are default columns from sources or calculated measures? if they are calculated measure that computed by DATE_FIN and DATE_LOG, you need to adjust formula to compute by new Dates table.

Hello @tringuyenminh92,

I have added extra date table DATE_COMMON and created "Many to One (*:1) relationship between date in DATE_COMMON and Finance_data and another  "Many to One (*:1) relationship between date in DATE_COMMON and Jira_data.

One of the relationship is NOT active - dashed line between fields.

Here is the error message I get when trying to tick the box "Make this relantionship active":

relationship_error.jpg

(note : StExportAll = Finance_data)

YTD values are calculated measures, calculated accordingly on 2 date tables, for each type of data: Finance and Jira.

Hi @Katerina_Bis,

 

It's correct. you need to choose one active and another inactive in this case. And for some aggregation in DAX, you could use userelationship() method to access inactive relationship for computing.

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.