ytd

[Katerina_Bis]

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

[tringuyenminh92]

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.

[Katerina_Bis]

Hello,

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

Hope it helps.

data_structure

[v-qiuyu-msft · 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

[Katerina_Bis]

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

[v-qiuyu-msft]

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

[tringuyenminh92]

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.

[Katerina_Bis]

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":

(note : StExportAll = Finance_data)

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

[tringuyenminh92]

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.