Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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
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.
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
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
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |