cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edavila
Helper II
Helper II

Amounts not showing Correctly

I have 2 tables one that has all the dates from 2 years listed and also the name of the month, week and year

 

and I create a report as table to show the Balance sheet for the company at a certain point of taime Jan or February so far, the balance on the table is correctly with my financial statements, but when I create a line chart and add the week on the Axis the balance that shows is the last amount for that week instead of adding all the amount for the Year to date. or even if I put the month will show the last amount for the month not the correct balance

Cash.PNG

 

25 REPLIES 25
parry2k
Super User
Super User

Make sure your month and  week values are unique in calendar (date) table, for example for two year (2016 and 2017), your months are like this:

 

 

 

Jan-2016

to

Dec-2017

not just Jan - Dec, with this your January data for both year wil be aggregated

 

Similar approach you to do for week, for example

 

Jan 1 - Jan 7, 2016 is 1st week and so on... 

 

and then create a hierarchary (seems like you are drilling down)

 

Year

Month  (new column as described above)

Week ( new column as described above)

 

and then use this hiearchary on your axis. Here is example link for  calendar table 

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





this is my date table

Date.PNG

As mentioned here are teh changes or add new columns:

 

Month should be "Month Name - Year", in this case Jan - 2016 and so on.

Week should be "Week - Month and Year" something like  "Week 1st - Jan 2016"

 

Two ways to do this:

 

- Add these column to your source table

- Create calculated column in Power BI Using DAX, let us know if you need help with this.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





PArry2k

 

I dont want to look more dumb that Im but, what you are saying is to add a new column to the table like

concatenate month name and year and concatenate( month name, year, week)

 

and I will need like and exmaple of the DAX formulas if is not too much to ask

 

thanks

You understood it correctly, what is your data source? I will send your DAX soon.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





data source is imported by excel

 

and thank you

Here are four columns you need to add, two are for sorting and you can hide those ones:

 

Month and Year = FORMAT('Calendar'[Date], "MMM-YYYY")

Month Sort = FORMAT('Calendar'[Date], "YYYY-MM")

Week and Year = "Week " & FORMAT('Calendar'[Date], "WW-YYYY")

Week Sort = FORMAT('Calendar'[Date], "YYYY-WW")

To sort, after above columns are added, go to modelling table, select "Month and Year" column and select "Month Sort" in "Sort by Column" drop down, do the same form "Week and Year", use "Week Sort"

 

- add hierarchary with Year, Month and Year and Week and Year" columns and now use this hierarchary on your axis.

 

I hope it will get you going.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





here is what I have after inputting all your DAX formulas

Capture1.PNG

 

the balance are still the last day of the month transaction not the su, of the whole year or YTD

what is "Amounts 2016" on values? Also can you share the relationship between your tables?






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

Date.PNGGL Accounts-2.PNGGL Accounts-3.PNG

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






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

Cash.PNG

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

GL Accounts-3.PNG

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.

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

Vvelarde
Community Champion
Community Champion

@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

 

relationshiptoboth.png

 

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

 

 

Chart.png

 

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.




Lima - Peru

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

Vvelarde
Community Champion
Community Champion

@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"

combine.png

 

 




Lima - Peru

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

 

 

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:

 

week.PNG

 

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.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.