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 community,
I'm currently working on converting old excel dashboards into Power BI at the company that I work.
There is an issue however that I'm currently have to work with data that only has years and months, though they are formatted as a number and string in seperate collumns.
One of the tasks that I've had was to create a chart that shows the last 13 months on a line chart. I've done this by creating a month number from the strings in the month collumn, and then combining the year+month to use for the X-axis.
The business however would like to see a simple notation of jan, feb, mar etc.
They would also like to see a filter/slicer for quarters in the future.
Is there a way to easily do this?
Thanks in advance,
Harm
Solved! Go to Solution.
Hi @HarmVanG,
Here is an other option that can achieve your requirement. The goal is to generate a date column, so, we first create calendar table. You don't need to create a relationship between date table and source table.
Dim date = FILTER ( CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ), DAY ( [Date] ) = 1 )
Then, in your source table, suppose, currently, you have had two columns [Month] and [Year], both are whole number type. Now, you need to create a calculated column.
Day = LOOKUPVALUE ( 'Dim date'[Date], 'Dim date'[Date].[Year], 'Year-Month'[Year], 'Dim date'[Date].[MonthNo], 'Year-Month'[Month] )
Best regards,
Yuliana Gu
Hi @HarmVanG,
Was above reply helpful to your scenario? If so, please kindly mark the corresponding reply as an answer so that it can benefit more people. If you still have any concern, please feel free to ask.
Best regards,
Yuliana Gu
Hi @HarmVanG,
Here is an other option that can achieve your requirement. The goal is to generate a date column, so, we first create calendar table. You don't need to create a relationship between date table and source table.
Dim date = FILTER ( CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ), DAY ( [Date] ) = 1 )
Then, in your source table, suppose, currently, you have had two columns [Month] and [Year], both are whole number type. Now, you need to create a calculated column.
Day = LOOKUPVALUE ( 'Dim date'[Date], 'Dim date'[Date].[Year], 'Year-Month'[Year], 'Dim date'[Date].[MonthNo], 'Year-Month'[Month] )
Best regards,
Yuliana Gu
My company recently released a free custom visual on App Source that allows custom labels on column charts. https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA200001785?tab=Overview With the advanced editor you can pick which columns or DAX measures to use for the labels.
@hat i would do @HarmVanG is create a date table, conver the month-year column, transform the year month to a 1 of every month and then you have the functionality fo what comes with a date table
apparently this creates a date column but i could be wrong i copied from elsewhere and didnt' validate
InsertedCustom = Table.AddColumn(Source, "Custom", each Date.FromText([Month] & " 15," & Number.ToText([Year])))
and then link it to this
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
Proud to be a Super User!
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |