How to get a dynamic average of transactions the first 90 days after a specific date

[Anonymous]

I am trying to analyze the performance of machines in number of transactions and compare this with the average of the rest of the machines. More specifically I want to look into how a specific machine performs the first 90 days after it is first operating. I have been able to find the number of transactions per machine its first 90 days in operation, and I have been able to find the general average for each machine the first 90 days in operation. Below I will give you more information on what I want to find: 

In example if a machine has its first day in operation 12.08.18, then I would like to have a measure that finds the average number of transactions of all machines from 12.08.18 and the next 90 days (in other words, what is the average number of transactions per machine in the period 12.08.18-12.11.18). In this way I can easily compare the new machine's performance when it comes to transactions the first 90 days with the average of all existing machines in the same period.

I have this measure to get the number of transactions the first 90 days of operation: 

Operation date 90+ = CALCULATE([Total number of transactions],DATESINPERIOD(DateTable[Date],FIRSTDATE(dimNode[operation_date]),90,DAY)

I think I have found a logical way to do it, but there are some things I can't figure out:

I have made a measure that gives the average number of transactions per machine: DIVIDE([total number of transactions], [total number of machines]). 

I will show you an example of how it looks when I put this into my report-object and one example of how I want it to be: 

Machine	Total number of transactions	Operation date	Transactions three first months	Average number of transactions all machines same period
XX1	1000	03.04.19	230	230
XX2	1500	04.05.19	120	120

As you can see with the current measure (used in: "Average number of transactions..." the 4th column gives the same value as the 3rd, this is naturally because it gets filtered on machine and operation date. I want it to be filtered on the operation date and 90 days forward from this date. But when I use the operation date-column from the dataset in my formula it only finds the average for that machine. In example with machine XX1 I want the measure to calculate the average total number of transactions in the period 03.04.19-03.07.19. 

This is how I want it to look like, maybe I will have to make an own object for the last column: 

Machine	Total number of transactions	Transactions three first months	Average number of transactions all machines same period
XX1	1000	230	310
XX2	1500	120	100

Hope this is understandable. 

[Anonymous · Accepted solution]

// Assumptions:
// Machines - dimension that stores machines
// Dates - dimension that stores dates (proper date table)
// Transactions - fact that joins to the others
// in the usual fashion:
// 		Transactions *:1 Dates
// 		Transactions *:1 Machines
// 
// What we really want is 2 measures:
// 1. One that for any set of currently visible machines
//    and any period of time will return the total number of
//    transactions (for the set of machines) in the period of
//    time that's defined as
//	  	[t1, t1 + (90 - 1) days],
//    where t1 = last day of the current period. Additional
//    requirement is that all 90 days must exist in the
//    Dates table. If not, return BLANK.
// 2. One that will return the same thing but for the set
//    of machines that are NOT visible in the current context.

// Measure you should already have:
[# Tx] // <- this is your total number of txn's

// First measure
[# Tx 90+] =
var __lastVisibleDate =
	MAX( Dates[Date] )
var __calcPeriod =
	DATESINPERIOD(
		Dates,
		__lastVisibleDate,
		90,
		DAY
	)
var __shouldCalc =
	COUNTROWS( __calcPeriod ) = 90
var __result =
	if( __shouldCalc,
		CALCULATE(
			[# Tx],
			__calcPeriod
		)
	)
return
	__result
	
// Second measure
// (C) stands for "Machine Complement"
[# Tx 90+ (MC)] =
var __lastVisibleDate =
	MAX( Dates[Date] )
var __calcPeriod =
	DATESINPERIOD(
		Dates,
		__lastVisibleDate,
		90,
		DAY
	)
var __shouldCalc =
	COUNTROWS( __calcPeriod ) = 90
var __result =
	if( __shouldCalc,
		var __machineComplement =
			except(
				all( Machines[MachineID] ),
				values( Machines[MachineID] )
			)
		return
		CALCULATE(
			[# Tx],
			__calcPeriod,
			__machineComplement,
			all( Machines )
		)
	)
return
	__result

From this you should be able to construct your measures...

[Anonymous]

This seems to be quite close to a solution @Anonymous 🙂  

But I have some questions:

1) The measures that you outlined in your post does not include the operation date (in the dimension that stores Machines) which represents the relevant date for the different machines to start counting transactions from. I think this in some way has to be included in DATESINPERIOD so that the measure is based on 90 days from the specific operation date. 

2) Measure number 2 does not seem to calculate the average of all the machines, only calculate the total number of transactions. Can I solve this simply by adding an Average() in front of [# Tx]?

3) In measure number 2 where you exclude all machines that already are visible in the context; to me it does not seem correct to do it in this way when I think about how the data is connected. I will give you some more information about this below:

A bit simplified dimMachine looks like this: 

Machine_ID	Operation_date
100XX	04.02.18
200XX	01.04.19
300XX	02.03.20

A bit simplified factTransaction looks like this:

Session	Machine_ID	Timestamp	Product_Name
10000XXX	100XX	05.03.18	99X
20000XXX	200XX	03.06.19	99X
30000XXX	300XX	04.06.20	99X

Session is unique for each transaction and is what the total number of transactions measure is calculated from

In addition to this I have a proper date table.

Thank you so far!

[Anonymous]

1) The measures that you outlined in your post does not include the operation date (in the dimension that stores Machines) which represents the relevant date for the different machines to start counting transactions from. I think this in some way has to be included in DATESINPERIOD so that the measure is based on 90 days from the specific operation date. 

First off, the measure works for ANY selection of date. In particular, if you select the start date of operation of Machine A and Machine A only is visible in the context, you'll get the answer you're after. You can connect the Date table to your fact on the operation date field, if you want. You are focused on a measure that will work for one arbitrarily selected machine only. But this measure can be extended to work for any selection of machines and this is what I've done - I have extended it, so that it works always and returns meaningful results. This, by the way, should always be done whenever possible. If you want to limit the measure to only work when there's only one machine visible, you have to add an IF( hasonefilter(....), ...) or IF( hasonevalue(....), ...) to the beginning of the measure, depending on what you want to obtain.

2) Measure number 2 does not seem to calculate the average of all the machines, only calculate the total number of transactions. Can I solve this simply by adding an Average() in front of [# Tx]?

It does not calculate the average, of course. The name of the measure does say it calculates the number of transactions in the same period of time as the first one but for the complementary set of machines. If you want to get the average per machine, you'll have to create a new measure, based on this one, that will divide it by the number of machines in the complementary set. That's easy to do. You can even take the measure and make slight adjustments to it to give you the average.

Additionally, if you want to compare the number of txn's for one machine and the average number of txn's for the rest of them, then why would you want to add the machine's data to the other machines' data? Does it make sense to mix data for these two sets? I believe it does not.

One last thing is that my code, of course, does not take into consideration the fact that a machine might not operate in the calculated period of 90 days because it started operation after the period. In this case, if you want to exclude such machines from the calculation, you have to adjust the code by filtering for only the relevant machines. This is easy as well.

This is the comment for the first of your posts. I'll take a look at the other one in a sec...

[Anonymous]

Some additional info just to try to clarify what I am trying to do @Anonymous.

Let's say I placed a new machine out 01.01.20, then I want the first measure you made to calculate the total number of transactions for this machine in the period 01.01.20-01.04.20. In the second measure I want to get the average of the other machines operating in this period, so this measure also has to be based on the operation date registered on the new machine, but it has to exclude this machine in the count of total number of transactions.

[Anonymous]

This is not clear. What's the definition of "operating in this period"? When does a machine operate in a any given period? It's not as simple as one might think. Let's say, the period is from 10 to 20. A machine started operation on 15. Does this machine operate in the period or not? By the way, since you have a date table, it makes sense to calculate your statistics for ANY date and ANY set of machines, not only for the operation start dates. Let's say, a machine starts operation on day 1 but you select day 20. You can also ask same questions about this machine and the averages. For instance: What's the number of txn's for this machine in the 90-day period starting from the selected date? What's the number of transactions for all the other machines that operated in the same 90-day period? What's the average number of txn's per machine in the same period? So, restricting your calculations to only the operation start dates is unnecessary... and will make code more complex (thus slower).

[Anonymous]

I forgot to answer this. But your solution worked when I fully understood what you meant. Thank you very much @Anonymous 🙂

[sturlaws]

Hi @Anonymous 

When you have machineID as a field in your table, it becomes part of the context where your measure is evaluated. So unless your change the context, your measure will be evaluated for just that one machineID. If you want find the average of all machineIDs, you must use the ALL- or ALLEXCEPT-funtions in your measure.

You have included the code for [Operation date 90+], but this measure is not used in the table. It is the measures [total number of transactions] and [total number of machines] that needs to be changed.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.