Measure: Calculate difference of two rows in a Matrix with a row filter and table filter

[Andreas_Wacker]

Hello PowerBI community 🙂

Trying to crack my head around following problem, which seems too big for my rookie knowledge in DAX.

Created a matrix which displays for each calendar week the target (calls) and actual (calls), filtered for every agent.

1. Matrix

Coulmn Filter = Name = by each Agent of Agent.Table

Row Filter = CalendarWeek = Date.Table

Value 1 = WCalls = one value for each agent per week in Target.Table

Value 2 = WTarget = counted rows from filtered Call.Table (Filter if Product = Call = 1)

Matrix Filter = Product in Target.Table

2. Table Model

AGENT.TABLE 1 -> ID -> * CALL.TABLE

AGENT.TABLE 1 -> ID -> * TARGET.TABLE

DATE.TABLE 1 -> DATE -> * TARGET.TABLE

DATE.TABLE 1 -> DATE -> * CALL.TABLE

3. Measure Result

Ideal would be the difference as a number (can be positiv/neutral/negativ) for a following up (conditional formatting).

I hope the description is clear. If not, the reason is my fried brain 😄

Thank you a lot for any help! 

Best

A

[Greg_Deckler]

@Andreas_Wacker So the basic process is the MTBF pattern below. You can implement this in a measure using a virtual table VAR and ADDCOLUMNS. Basically, get your table of data in your VAR to mimic the visual, same summarization, etc. Then use ADDCOLUMNS to find previous row and grab the value you want. 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[Andreas_Wacker]

@Greg_Deckler Thank you for your quick and detailed reply. 

I tried to grasp it, but my meausres aren't creating any usefull data to visualize.

But I have another idea, to reach my goal.

I have in the Target.Table following columns:

Member.ID, Start Date of the Week, End Date of the Week, Target Call

Call.Table

Member.ID, Date 

Is it possible to create one new column in Target.Table which is counting rows of the table call.table. with two filters. 

1. Filter = ID of Team Member 

2. Filter = Date of Call, between Start and End of the Week

My calculation so far:

Column Call =

CALCULATE(

COUNTROWS(call.table),

FILTER('target.table',Contains('call.table',[ID],'target.table'[ID])),

FILTER(Date between))