Forum Discussion

mflaursen's avatar
mflaursen
Regular Visitor
8 years ago

Custom targets in column chart

I am trying to make a clustered bar chart. Two columns for each month. One column is target, the other is actual value.    The actual values come from a data source consisting of a column of proces...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

I think you don't use a date table, which is why the measure is showing the total of all values, instead of the value per month.

If you don't use a date table yet, you should try it. It is very handy when filtering multiple columns from different tables.

Here is the DAX formula to create such date table (Model > new table):

 

DateTable = ADDCOLUMNS ( CALENDAR (DATE(2001,1,1), DATE(2020,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "MMMM" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

Then make the relationships between the date table and the relevant tables. When you add the month from the date table, it should show the correct values.

Please let me know if this works.

mflaursen's avatar
mflaursen
Regular Visitor
8 years ago

Still does not seem to work :( I have connected the "Date" column to the "Lotmovementdate" in the lotmovmentdata and the "Target date" in the Goals table.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi mflaursen,

     

    I'd like to suggest you change cross filter direction to 'both' for these tables and try again, it seems like power bi can't use current relationship to analysis and gripping your records.

     

    Regards,

    Xiaoxin Sheng