Create a month field from a direct query field

[helen_p]

Dear Power BI community

I have not yet explored the options of using Master calendars, and am about to embark on that journey

However for an interim, short term solution I am looking to create a new column called MonthYear from a Date time field which has come from a direct query and is diaplayed as DD/MM/YYYY HH:MM...I understand that you can not create a direct hierachy off the date field when using direct query

1) Firstly I changed the format of the Date/time field to Date DD/MM/YYYY

2) From reading various other posts I created a  new column called MonthYear and basically used the DateTime field 

MonthYear = Referral[Referral received date]

3) Using the Date Type field I change the type to Date and using the formatting I changed the format to YYYY-MM

4) I then right clicked and created a new date hierachy on the original field called Referral[Referral received date] and dragged MonthYear into it 

The outcome is when I used MonthYear in a visual it does not group up and still displays as individual dates in a visual

Can someone help me create a MonthYear field when using direct query which will actually group counts up into a MonthYear?

Kind Regards

Helen

[helen_p · Accepted solution]

Hi there

Thank you for your response, it is much appreciated and I will certainly look into that.  As an interim solution I created a calendar table in my report which calculated the relevant time frames for me:

Calendar = var _cal = CALENDAR(Min(Referral[Referral received date]), Max(Referral[Referral received date])) Return ADDCOLUMNS(_cal, "Year", Year([DATE]), "Month Num", MONTH([Date]), "Month Name", Format([Date], "mmmm"), "MonthYear", Format([Date], "mmmm-yyyy"))

Kind Regards

Helen

[v-rzhou-msft · Accepted solution]

Hi @helen_p 

I think your problem may be caused that when you use Direct Query to connect to your datasource, you couldn't use "Format" function in calculated column to get MonthName and MonthYear.

You can try switch function to get MonthName and MonthYear.

MonthName =
SWITCH (
    Referral[MonthNum],
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8, "August",
    9, "September",
    10, "October",
    11, "Novement",
    "December"
)

MonthYear = Referral[MonthName]&"-"&Referral[Year]

Result is as below.

Best Regards,

Rico Zhou

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

[helen_p · Accepted solution]

Hi there

Thank you for your response, it is much appreciated and I will certainly look into that.  As an interim solution I created a calendar table in my report which calculated the relevant time frames for me:

Calendar = var _cal = CALENDAR(Min(Referral[Referral received date]), Max(Referral[Referral received date])) Return ADDCOLUMNS(_cal, "Year", Year([DATE]), "Month Num", MONTH([Date]), "Month Name", Format([Date], "mmmm"), "MonthYear", Format([Date], "mmmm-yyyy"))

Kind Regards

Helen

[amitchandak]

@helen_p , Even if change format a date time field would remain date time.

It is best that you bring a date without time(12 AM is fine) and then join to with date table to display month year.

Date table can be there in database or can  be created on power BI

Refer for date table in power bi for direct query : https://www.youtube.com/watch?v=24arfrD3Qzk&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=6