Calculation using distinct counts form two different matrix tables

[jahlum1]

I have two separate matrix tables with the data below -

Matrix 1:

Week of the Year [YRWK] a record was completed

Count of records completed during each YRWK [Count of TD_comp]

Matrix 2:

Week of the Year [YRWK_created] a record was created

Count of records created during each YRWK_created [Count of TD_created]

How would I go about creating a calculation using these separate tables that is [Count of TD_comp] - [Count of TD_created] for each Week of the Year?

[v-achippa]

Hi @jahlum1,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @AmiraBedh, @Jihwan_Kim and @Bmejia for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @jahlum1,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

[Bmejia]

Just to add to conversation to Jihwan_Kim post.  I would probably make the Period a dynamic table the way you won't have to be entering the YRWK everytime you have a new value.  The table below would look at the max YRWK and Min YRWK from both tables to generate a list.

Period =

VAR MinYYWK = IF(Min(Completed[YYWK])>Min(Created[YYWK]),Min(Completed[YYWK]),Min(Created[YYWK]))

VAR MaxYYWK = IF(Max(Completed[YYWK])>Max(Created[YYWK]),Max(Completed[YYWK]),Max(Created[YYWK]))

RETURN

GENERATESERIES(MinYYWK,MaxYYWK,1)

[AmiraBedh]

Hello !

Thank you for posting on Microsoft Fabric community.

You need to reate a date table with a date column and a YrWk key for exampleYYYYWW and creare 2 relationships with your fact table twice:

• active: date[date] to fact[createddate]

• inactive: date[date] to fact[completeddate]

TD Created =
CALCULATE(
    DISTINCTCOUNT(fact[TD #]),
    USERELATIONSHIP('date'[date], fact[createddate])
)

TD Completed =
CALCULATE(
    DISTINCTCOUNT(fact[TD #]),
    USERELATIONSHIP('date'[date], fact[completeddate])
)

TD Delta = [TD Completed] - [TD Created]

then put 'date'[YrWk] on the matrix rows and show [TD Delta].