03-20-2017 02:16 PM
Amounts 2016 is the amounts (sorry to repite myself) of each transaction and by each date of the month and year and will be a YTD
the thing is when I insert a table the amounts are ok as SUM, but when i plot chart is not getting the sum amount as total just the last day or the week transactions
03-20-2017 03:05 PM
I believe in this example your using Date and FY 2016 tables, correct? On which column these tables are connected?
Also can you share the screen shot where you incorrect result on your graph
03-20-2017 03:10 PM
Parry2k, first of all I want to thank you for taking the time to help me and I know that you have many other things to do, Thank you
and the screen shot I have already send it before here it is again
as you can see these 2 tables are the same information the one on the left is the balance sheet and the one on the right is just filtered by the cash in bank and added the week and the remove the Mapping "condition", the one on the right as you can see the amount of the week 10 is half way to 1M and the table has 12M not even closed, but I figure that is only bringing the value of each week and not adding the values for the whole account
are these relationships correctly done?
03-20-2017 03:13 PM
I noticed that you provided the shot in your original post (my mistake), can you answer on what field your FY 2016 and FY 2017 tables are related with date table?
If possible, send me excel sheet with sample data and I will try to look at my end.
03-20-2017 03:21 PM
the are connected by the date on the dateSH table and on the FS thru the posting date
here is the link of the files
03-20-2017 03:57 PM
On thing I noticed that in your table you don't have Week column in it, so basically it is adding up all the Amounts for "Cash in Bank" for all week 10 - 2016" and doing the same for other account, here is quick shot for you:
Also as a best practice it make sense to combine both "FY" sheet in one table and then use that in PowerBI, let's keep that as a seperate topic.
03-20-2017 04:26 PM - edited 03-20-2017 04:28 PM
Hi, do you want YTD of Amount 2016?
If answer is yes, i propose do some changes:
1. Change relationship between FS2016 and DateSH to Both
2. Create a measure:
Amount2016YTD = CALCULATE ( SUM ( 'FS 2016'[Amount 2016] ), FILTER ( ALL ( DateSH ), DateSH[date] <= MAX ( DateSH[date] ) ) )
3. Put this measure in your chart
4. Finally, to order your weeks you should change your WeekSort Column
A way to sort is this:
Week Sort = IF ( DateSH[Week] < 10, DateSH[Year] & "-0" & DateSH[Week], DateSH[Year] & "-" & DateSH[Week] )
Like Parry recommend , yo should use only a FS2016-2017 to simplify things later.
03-21-2017 06:46 AM
thank you and let me ask you another question if I combine both tables what Im going to do when I run out of space in the excel sheet it only has like a 1M rows
03-21-2017 08:00 AM - edited 03-21-2017 08:01 AM
You can combine in Power BI Desktop.
Import your both tables and go to Edit Query to do this. --Select thr action "Append Queries"
03-21-2017 08:03 AM
to clarify do I need to create a new table and then append both tables and let me tell you that I change the direction of the relationship for FS2016 to both and FS2017 has a single when I try to change it will give and error