Why are my measures looking this? How can I have it look normal on a bar chart?

[mespiritu]

[mespiritu]

The measures I added on the Y axis were from added "Custom Columns" and I used a custom column formula (if then calculation) to get the sum of each month. 

[ryan_mayu]

could you pls provide some sample data?

if you unpivot the columns ,you only need to create one measure to display all charts.

[mespiritu]

Sure. To give you some context. Each month updating the report, I have to provide a visual of 12 rolling months (I have already figured how to calculate that with my data) the following Columns Monthyear was from adding custom columns by using a custom column formula per Month and date. Goal is to put the sum of each month on a visual chart. 

[mespiritu]

Sample data screen shot

[ryan_mayu · Accepted solution]

in PQ select the first three columns and unpivot other columns

then you just need to drag attribute to x-axis and value to y-axis

[mespiritu]

Hi thank you so much for working with me and finally came across a solution. I did everything mentioned above, but only difference is that I created a duplicate table, so that my other data values in my original data table stay the same.  With the duplicated table, I removed all other columns and kept my unpivoted columns only and finally able to visually chart the 12 rolling months. 

[ryan_mayu]

you are welcome

[mespiritu]

While trying to apply changes above I'm receiving this error about a client ID that contains a duplicate in the SECTOR table that's consider the primary key table - I double checked and there is no duplicate so I'm a bit confused. 

[ryan_mayu]

it's not a good way to present your data. usually we will unpivot your data in the power query, then all the date will be in a column and all value will be in another column

https://prod.support.services.microsoft.com/en-au/office/unpivot-columns-power-query-0f7bad4b-9ea1-4...

[aduguid]

I'd use a calendar table and join to that in the model view. Then you can can use the month from the calendar in the X-axis of the chart. After that you can just use one measure to calculate the sum.

Here's an example of a calendar that I use quite frenquently.

Calendar = 
VAR _today_date =        TODAY()  //'Properties'[Today Date]
VAR _fiscal_year =       YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
        //CALENDARAUTO()   //range of dates is calculated automatically based on data in the model
        CALENDAR(_fiscal_year_start, _fiscal_year_end)
        , "Calendar Year Period End",   FORMAT([Date], "yyyy12")
        , "Calendar Year Period Start", FORMAT([Date], "yyyy01")
        , "Calendar Year Period",       FORMAT([Date], "yyyyMM")
        , "Calendar Year Quarter Nbr",  QUARTER([Date])
        , "Calendar Year Quarter",      FORMAT([Date], "\C\Yyyyy \Qq")
        , "Calendar Year Half",         FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0)
        , "Calendar Year",              YEAR([Date])
        , "Day Name Short",             FORMAT([Date], "DDD")
        , "Day Name",                   FORMAT([Date], "DDDD")
        , "Day Of Week",                WEEKDAY([Date])  
        , "Day",                        DAY([Date])
        , "Fiscal Year Period End",     FORMAT(EDATE([Date], 6), "yyyy12")
        , "Fiscal Year Period Start",   FORMAT(EDATE([Date], 6), "yyyy01")
        , "Fiscal Year Period",         FORMAT(EDATE([Date], 6), "yyyyMM")
        , "Fiscal Year Quarter Nbr",    FORMAT(EDATE([Date], 6), "q")
        , "Fiscal Year Quarter",        FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq")
        , "Fiscal Year Half",           FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0)
        , "Fiscal Year",                YEAR(EDATE([Date], 6))
        , "Is Current FY",              IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0)
        , "Is Future",                  IF([Date] > _today_date, 1, 0)
        , "Is Past",                    IF([Date] < _today_date, 1, 0)
        , "Month End",                  EOMONTH([Date], 0)
        , "Month Name Short",           FORMAT([Date], "MMM")
        , "Month Name",                 FORMAT([Date], "MMMM")
        , "Month Start",                DATE(YEAR([Date]), MONTH([Date]), 1)
        , "Month",                      MONTH([Date])
        , "Week Ending",                [Date] + 7 - WEEKDAY([Date], 1) // Saturday
        , "Week Starting",              [Date] - WEEKDAY([Date], 1) + 1 // Sunday
        , "Week of Month",              1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 )
        , "Week of Year",               WEEKNUM([Date])
        , "Week of Fiscal Year",        IF(MONTH([Date]) < 7
                                        , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1)
                                        , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1)
    )

RETURN
_result

[jdbuchanan71]

The chart is leaving room on either side in case you add a category to the X-axis.  You would be better off stacking the data into just a pair of columns (Date and Enrollment), adding a date table and putting month-year on the X-axis.