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:
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)
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
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.
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
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.
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
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
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?
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
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.
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"
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.
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
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.