Calculate Measures in two tables with different date columns (per month view required)

[Anonymous]

Hi all,

I really need your expertise with PowerBI Measures.

I have two source tables Incidents [ID, Incident Date, ...]  and Changes [ID, Change Date, ...] from which I want to calculate an incident per change rate on a monthly basis.

Therefore, I created two Measures to calculate both values as per different filters. So far so good:

Team A Change Count = CALCULATE(COUNT(Changes[ID]),'ChangeCreatorTeam'[Company]="Team A")

Team A Incident Count = CALCULATE(COUNT(Incidents[ID]),'ChangeRel'[Change Implementor]="Team A")

Now, I want to calculate     Rate X = Team A Incident Count / Team A Change Count    per month.

However, when I further try to calculate / visualise per month, it doesn't work as the two values refer to different "date" columns in their respective table.

Any idea how I can calculate the final rate per month? I hope the problem is clear to you. A million thanks for your support!

[Greg_Deckler]

@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Anonymous]

@Greg_Deckler Thank you for your prompt reply! But I am sorry, I think I did not get it yet...

You're saying I need to calculate the Min or Max values (shouldn't matter as the counts are static for the month, right?) out of my Measures? And then use these values to calculate the required quotient?

[Greg_Deckler]

@Anonymous Need sample data as text in a table and expected output from the sample data to be more specific.

[Anonymous]

@Greg_Deckler Sure, please see sample data below.

Incident Table

Incident ID	Incident Date	Team	Category
10002	23.05.2020	Team A	Storage
10003	30.05.2020	Team B	Network
10004	30.05.2020	Team A	Compute
10005	01.06.2020	Team B	Storage
10006	23.06.2020	Team C	Network
10007	04.07.2020	Team A	Storage
10008	05.07.2020	Team B	Storage
10009	10.07.2020	Team A	Network
10010	07.07.2020	Team B	Compute

Change Table

Change ID	Change Date	Team	Change Type
201	23.05.2020	Team A	Emergency
210	23.05.2020	Team B	Standard
202	30.05.2020	Team B	Standard
203	30.05.2020	Team A	Standard
211	30.05.2020	Team A	Standard
212	30.05.2020	Team B	Standard
204	01.06.2020	Team A	Standard
213	01.06.2020	Team A	Standard
205	23.06.2020	Team B	Standard
206	04.07.2020	Team A	Standard
207	05.07.2020	Team B	Standard
209	07.07.2020	Team A	Standard
208	10.07.2020	Team C	Emergency

I want to calculate / visualize the quotient (Incidents / Changes) per Team and month.

Example:

I am calculating the following counts using the measures above:

Team A:

- 2 Incidents, 3 Changes in May

- 2 Incidents, 2 Changes in July

Out of these numbers, I want to calculate the quotient Incidents per Changes (in this case 2/3=66,66% in May and 2/2=100% in July) and display it in a clustered bar chart with multiple teams per month.

Of course, I still want to be able to apply different filters (e.g. Change Type). Therefore, it must be somehow dynamically.

Thanks a million for your support!

[v-kelly-msft]

Hi  @Anonymous ,

First create a month column in both 2 tables;

Then create 2 slicer table as below:

Then create a measure as below:

Rate X = 
var _ChangeCount = CALCULATE(COUNT('Change Table'[Change ID]),FILTER(ALL('Change Table'),'Change Table'[Team]=SELECTEDVALUE('Slicer Table2'[Team])&&'Change Table'[Month]=SELECTEDVALUE('Slicer Table1'[Month])))
var _IncidentCount =CALCULATE(COUNT('Incident Table'[Incident ID]),FILTER(ALL('Incident Table'),'Incident Table'[Team]=SELECTEDVALUE('Slicer Table2'[Team])&&'Incident Table'[Month]=SELECTEDVALUE('Slicer Table1'[Month])))
Return
DIVIDE(_IncidentCount,_ChangeCount)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,

Kelly

If I have solved your issue,pls mark the reply as answered to close it!

[richbenmintz · Accepted solution]

Hi @Anonymous,

I think you are going to want to turn your model into a star schema with at least a date table and a team table

then you can create super simple measures like

Changes = COUNTROWS('Change Table')

Incidents = COUNTROWS('Incident  Table')

Incident Per Change = DIVIDE([Incidents], [Changes])

which will provide the following results

The link below provides a pbix file with the tables and measures suggested.

sample.pbix 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

Thank you! It's often easier as expected. I used a separate "Date" table and linked both tables to the "Date" table. When using the separate dates instead of the original columns, I achieved the right aggregation. Thank you for your support!