Reply
Regular Visitor
Posts: 26
Registered: ‎03-20-2017

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

 

New Contributor
Posts: 630
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

[ Edited ]

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 

 

 

Regular Visitor
Posts: 26
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

this is my date table

Date.PNG

New Contributor
Posts: 630
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

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.

Regular Visitor
Posts: 26
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

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

New Contributor
Posts: 630
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

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

Regular Visitor
Posts: 26
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

data source is imported by excel

 

and thank you

New Contributor
Posts: 630
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

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.

Regular Visitor
Posts: 26
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

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

New Contributor
Posts: 630
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

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