Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Solved! Go to 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:
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.
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.
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:
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |