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.
I have a measure I wrote to conditionally format a text box based on which filter options are currently selected.
This works fine, with the exception that the month and quarter information does not display as I would expect.
In the example above, I would expect the output of the conditionally formatted text box to read: Jan 2019, Apr 2019, Jan 2020, Feb, 2020. However, it is leaving out the year and grouping same-name months together (i.e. January in this case). I also have this same issue when selecting by quarter.
I was under the impression that FORMAT(value, "MMM yyyy") and the quarter eqivalent of FORMAT(value, "q yyyy") would result in my desired output. Is there something that I am missing?
Solved! Go to Solution.
@markmess77 , Create a month table like this have Qtr -year and other build in that
MonthTable =
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])
You create a new column in your date table in the required format and use Hierarchical slicer from feb release
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2020-feature-summary/
@amitchandak I have a table of distinct dates in the format that I would like already. However this is by month, but my desired result would be for this to apply by quarter as well. I assumed that implementing the format function would accomplished this
@markmess77 , Create a month table like this have Qtr -year and other build in that
MonthTable =
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |