Bar chart with two columns with the same weekday name

[Anonymous]

Hello experts! 

I came upon an interesting challenge. 

So, what I pretend to show is the chart below. If today is Monday, I want to show today, and the last week (including the monday before today):

In power BI, if I add the last monday, it will group to today (if today is also monday), so instead of two monday as shown in the chart, I have one monday that has both days summed.

I had an idea to create a column where I give different names to weekdays before than last 7 days, like "Mon -1", so, this way, I never have two of the same names on the chart.

But I can't seem to make it work. I am working in direct query. 

Thank you for your help in advance! 🙂

[Anonymous · Accepted solution]

Thank you everyone for your help ! 🙂

The easiest way I found to achieve this, is to do a concatenation directly in the DB. So I have the column that I need right away on Power BI.

[Anonymous]

Hi @Anonymous ,

You can't create a column on your raw table but you can create a calculated table like CALENDARAUTO() and create a relationship between these two table and then create a calculated column on the CALENDAR table.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VasTg]

@Anonymous 

I would create weeknum from dates and concatenate it with day and use that in the graph.

Column = COMBINEVALUES("-",WEEKNUM('Table'[Column1]),FORMAT('Table'[Column1],"ddd"))

Update: Or use the dax to get what you asked for.

Column 2 = IF('Table'[Column1]>TODAY()-7,FORMAT('Table'[Column1],"ddd"),CONCATENATE(FORMAT('Table'[Column1],"ddd"),"-1"))

If it helps, mark it as a solution

Kudos are nice too 

[Anonymous]

Hey!

I am trying to write that code, but for some reason it's returning an error after FORMAT.

Was that a custom column?

[VasTg]

@Anonymous 

"New Column" in desktop...Not Query editor window.

[Anonymous]

It doesn't let me use the FORMAT, because I am working in Direct Query :s