cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Danbarton88
New Member

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

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 

1 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

View solution in original post

4 REPLIES 4
Danbarton88
New Member

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 dateend DateTotal of transactions
John doe01/02/201423/04/2015£15,560

 

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

 

 

Transaction amountTransaction dateDescriptionReference
100014/02/2014Purchase 1231
1400016/07/2014card payment4123
56016/07/2014Cash1231

 

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 

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

View solution in original post

RJ
Resolver II
Resolver II

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
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors