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

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.

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

      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

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

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