Display the date

[joeydeacan]

Morning all,

I wonder if someone could help, I been experimenting with Power BI and Ive got to say I love, Im fairly new to both BI and SQL tho.

I am linking directly to a SQL direct Query, everything on my dashboard is perfect, apart for the Date is displayed without the day of the week, I have tried going into the formatting but DDDD is not aviable just DD.

Below is an example of what I have got :-

If anyone could walk me through it I would be most grateful. everything I have tried says cannot be performed on a Direct Query.

deliveredTotal_UnitsTriplesTghTghAreaLamFloatFloatAreaShapesGeoQLeadQSix_mmSix_mmUnitAreaOver_199

29/08/2017 00:00:00	656	null	337	657.29	30	289	235.06	28	24	17	17	32.89	31
01/09/2017 00:00:00	460	33	196	223.41	null	259	187.55	null	83	4	null	null	null
24/08/2017 00:00:00	921	null	422	709.59	169	335	260.76	32	47	18	9	21.35	15
30/08/2017 00:00:00	1197	null	450	671.78	59	688	535.64	4	223	4	2	5.18	23
25/08/2017 00:00:00	1101	61	549	859.08	84	469	385.04	83	113	32	9	5.02	19
31/08/2017 00:00:00	759	2	327	489.67	20	412	332.96	22	36	6	2	5.79	3

[mede]

Calculated Column:

Day = FORMAT(Dim_Date[Date], "dddd")

[joeydeacan]

Thanks,

Any chance you could walk me through a little, ideally i would like to format to be dddd/dd/mmm

So day, then date then month ?

[mede]

Hi @joeydeacan

I am not 100% sure if you are able to add calculated column in direct query mode.

But normally, you can get that format by simply adding calculated column:

 = FORMAT(Dim_Date[Date], "dddd/dd/mmm")

[joeydeacan]

Cheers mate , but I dont think I can do that on DirectQuery Mode.

Maybe I can do something with the Query, but Im not that good either.

[mede]

This is also easy to achieve in query editor.

Select your date column; go to "Add Column" tab; Date dropdown; you generate three new columns: Day Name, Day, Month Name

Then, simply merge those three new columns using a delimiter such as "/" to get the format you need:

Let me know if this works.

If query editor is also not allowed, then you are supposed to do it in SQL directly in the data source.

[joeydeacan]

**bleep** thanks man , it does work, but not in DirectQuery tho, wont let me add it in.

Arrrgggghhhh so close, thanks for your help tho .