Forum Discussion

edavila's avatar
edavila
Icon for Helper II rankHelper II
9 years ago

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 ...
Need Help
Show and Tell
parry2k's avatar
parry2k
Icon for Super User rankSuper User
9 years ago

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.

edavila's avatar
edavila
Icon for Helper II rankHelper II
9 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    9 years ago

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

    • edavila's avatar
      edavila
      Icon for Helper II rankHelper II
      9 years ago

      data source is imported by excel

       

      and thank you

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        9 years ago

        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.