Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.