Amounts not showing Correctly

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

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?

thank you

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.

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

thanks

https://www.dropbox.com/sh/0bfiodh0vaid65l/AAD0OHEpSkttRdrEzT22mazca?dl=0

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.

edavila

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.

Victor/Parry

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

edavila

You can combine in Power BI Desktop.

Import your both tables and go to Edit Query to do this. --Select thr action "Append Queries"

Victor

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

Parry2k

one question that I have is when I run out of space in the excel sheet and I will need to add more information, how this will be done to import information to the same sheet.

as Vvelarde mentioned, you can still have seperate sheet but you can combine those in PowerBI using Append.

Thank you and know I run into another problem as I combine both sheets into one and my number does not show for each year as separated and they are cero as no conection has between them.

know what I think has to be done here is create a new column and just add and IF formula to get everything that belong to 2016 into that column and another for the 2017 as well, Im correct

Thanks

I looked at your sheet, and you renamed amount to amounr 2016 and amount 2017 respectively, just don't rename those columns, leave it as amount and then use append query.

basically what you want is FY 2016 is append to FY 2017 (or vice versa) and all the rows are appended to existing table and not added as extra column, 

PArry2k

now is adding correctly but all the accounts are mess up as instead of gruping like before they sread all over and the cash balance is ok but I cannot set it up as a line chart

https://www.dropbox.com/sh/0bfiodh0vaid65l/AAD0OHEpSkttRdrEzT22mazca?dl=0

I was able to put amount on the 2016 -2017 but the cash graph is not working

thanks