How to show transactions between two dates that differ for each row

[Danbarton88]

Hi All

I am currently having a problem in showing transactions ( the transactions are whole number value amounts) that fall between a start and end date for an employee. The crux of the problem is that the start and end date differ for each employee, so filtering for the data is a problem. I have explained the full issue below: 

I have two tables that I need to link between. One is a list of all transactions made by each employee (employee name, HR number, value, date) , the other is a listing of the start and end date of each employee (employee name, HR number,start date, end date), What I want to show on the the desktop is a the sum total of the transactions by employee between the start and end date and then ideally a breakdown below of each transactions that makes up that sum total in a seperate table that is filtered by the employee using the dynamic filter. 

So far I have managed to work out the sum total by adding on a new colum to the start and leave date table and using the following formula :

Total spend = CALCULATE (
SUM( Transactions[Total] ),FILTER(Transactions, Transactions[HR number] = 'HR start end date'[HR number]),DATESBETWEEN(Transactions[Date],
'HR start end date'[Start date],
'HR start end date'[End date]
)) 

 'Transactions' is my transactions table

'HR start end date' is my star and end date table 

This forumla gives me my total figure but I cannot get it so that I can see the transactions that make up the total amount on the desktop page even with a relationship between the two tables as it just shows the total transactions by the employee and does not filter it for the start and end date. 

I keep thinking there must be a better way of doing this that uses the PowerBI desktop features but I cannot come up with it. 

I cannot upload anything to help as it has sensitve data on it.  

Any help would be much appreciated! 

Regards

Dan 

[Danbarton88]

Thank you both for your help. To clarify in a little more detail, I have managed to get the following data using the calculate formula I attached above that shows the sum amount of transactions that falls between the start and end date, but what I would like to do is show the individual transactions that make up the sum amount. 

I currently have : 

employee name	Start date	end Date	Total of transactions
John doe	01/02/2014	23/04/2015	£15,560

What I would also like to show as well is the transactions that make up that amount 

Transaction amount	Transaction date	Description	Reference
1000	14/02/2014	Purchase	1231
14000	16/07/2014	card payment	4123
560	16/07/2014	Cash	1231

Ideally on a powerBI desktop page, what I would want is a bar chart showing the spend by individual(from the data in the top table) that I could click on that would then filter a transactions table underneath to show what transactions make up that total amount. 

It may not be possible, but at the moment I'm having to manually filter for the dates for the relevant employee to show the transactions that make up the total sum of transactions for that period and I'd like to be able to do it automatically 

[RJ · Accepted solution]

Hi

This should be so easy. Im unsure why you are having a problem doing this

Just set up a column chart with dimension = staffname and sum (Amount)

and a matrix / or table with the required dimensions and sum(Amount)

click on the bar chart as required to drill down

edit But you realy need to be a lot clearer as to what you want or what your problem is. Im still not sure

[RJ]

I read this but am unsure what you want to do

I assume you want to filter by a transaction period?

Why not enter in  a table

Employee Name

Start Date

End Date

Sum (Amount)

and filter as required

[Greg_Deckler]

Admittedly only having read this briefly, what I think that you should check out would be the new CALCULATETABLE function.

https://support.office.com/en-US/article/CALCULATETABLE-Function-DAX-873367a4-0f07-436a-a756-b64e9ee...

If I understood what you are going for, this might fit the bill.