Forum Discussion
Solved
Calculation using distinct counts form two different matrix tables
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: We...
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.
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.
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)