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
Monday - last edited Monday
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.