Forum Discussion
Problem with Month-Year view
Dear all,
I have a column formatted as date (called "_Vorauss. Baubeginn"). It includes data with day, month and year.
Now I would like to get the data aggregated to just month/year to use that in a filter.
My idea was to extract the year and mont (by using the MONTH/YEAR formular) and use the DATE forumla by entering Year Baubeginn, Month Baubeginn and 1 (as day).
Unfortunately I´m not able to select the year or month I extracted previously.
Do you have an idea how I can solve this? Or am I thinking to complicated and there is a much easier way to get the month/year values.
Thank you very much in advance for your help.
Hi grasa
Thanks for the reply from 123abc .
Do you need to display the data of the month selected in the filter in the table visualization? If I understand correctly, please refer to the following test, in my test, I use DiectQuery mode to connect two tables, one of the tables as a filter, if the data structure I use is different from yours, please feel free to correct me.
Table_1
filter_table
Then I created a measure as follows.
Measure = IF(SELECTEDVALUE(filter_table[Date]) = BLANK(), 1, IF(YEAR(MAX([Date])) = YEAR(SELECTEDVALUE(filter_table[Date])) && MONTH(MAX([Date])) = MONTH(SELECTEDVALUE(filter_table[Date])), 1, 0))Put the measure into the visual-level filters, set up show items when the value is 1.
Put the Date field of filter_table into Filter
Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks to all for your help, I think I found a solution that works for me: I created a new calendar table which includes all the dates from my "_vorauss.Baubeginn" column. Within this table I created a new mmmm-yyyy column.
8 Replies
To aggregate your date column (called "_Vorauss. Baubeginn") by month and year for use as a filter in Power BI, you can follow these steps:
Create a calculated column that extracts the month and year from your date column "_Vorauss. Baubeginn".
You can use the following DAX formula to create a new column that shows the first day of the month for each date in your original column:
MonthYearColumn = DATE(YEAR([_Vorauss. Baubeginn]), MONTH([_Vorauss. Baubeginn]), 1)
This will give you a date formatted as the first day of each month, which can be used to group and filter your data by month and year.
Use the calculated column in a slicer or visual:
Now that you have a column with the year and month, you can use it in a slicer to filter your data by month and year.
Alternative Approach (Using FORMAT):
If you don't need the exact date and only want a textual representation (e.g., "January 2024"), you can create a calculated column with a formatted string for month and year:
MonthYearText = FORMAT([_Vorauss. Baubeginn], "MMM YYYY")
MonthYearText = FORMAT([_Vorauss. Baubeginn], "MMM YYYY")
If you have any issue please feel free and contact with me.
123abc thank you very much for your fast reply! I tried your first option but unfortunately it says that there weren´t found any data for that visual.
And I´m not able to try the second option, there occures an error which says that the function "FORMAT" is not allowed in DAX expressions for calculated columns in DirectQuery models 😞...
Thank you for your feedback! Since you're using DirectQuery, it limits some of the DAX functions like FORMAT, which causes the error you're seeing. Let's tackle this issue by adjusting our approach to work within the DirectQuery constraints.
Option 1: Workaround for Date Aggregation in DirectQuery
We can avoid using functions that are not supported in DirectQuery, like FORMAT, and rely solely on DAX functions that are allowed.
Step 1: Extract Year and Month
Instead of using the FORMAT function, let's directly extract the year and month in numeric form and combine them.
Create two calculated columns for Year and Month:
YearColumn = YEAR([_Vorauss. Baubeginn])
MonthColumn = MONTH([_Vorauss. Baubeginn])Step 2: Combine Year and Month
Since DirectQuery doesn’t allow FORMAT, we’ll combine the year and month into a new column as text without FORMAT:
YearMonth = [YearColumn] * 100 + [MonthColumn]
This will create a column like 202401 for January 2024, which you can use as a slicer or in visuals.
Alternatively, if you want a date for the first of the month, this formula will work:
FirstOfMonth = DATE(YEAR([_Vorauss. Baubeginn]), MONTH([_Vorauss. Baubeginn]), 1)
Step 3: Use the New Column in Visuals
Now, use either the YearMonth or FirstOfMonth column in your visuals or slicers to filter by month and year.
Option 2: Modify Your Data Model (If Possible)
If you have control over your data source, you could consider switching to Import Mode rather than DirectQuery for more flexibility. Import Mode allows you to use functions like FORMAT and gives you more control over transformations.
Let me know if you encounter any further issues, and we’ll continue to refine the solution!
Hi grasa
Thanks for the reply from 123abc .
Do you need to display the data of the month selected in the filter in the table visualization? If I understand correctly, please refer to the following test, in my test, I use DiectQuery mode to connect two tables, one of the tables as a filter, if the data structure I use is different from yours, please feel free to correct me.
Table_1
filter_table
Then I created a measure as follows.
Measure = IF(SELECTEDVALUE(filter_table[Date]) = BLANK(), 1, IF(YEAR(MAX([Date])) = YEAR(SELECTEDVALUE(filter_table[Date])) && MONTH(MAX([Date])) = MONTH(SELECTEDVALUE(filter_table[Date])), 1, 0))Put the measure into the visual-level filters, set up show items when the value is 1.
Put the Date field of filter_table into Filter
Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry for the late reply. I don´t need the data in the filter section but as a visual like this, but only showing months and years, without days:
Unfortunately the solution from 123abc about handling blanks wasn´t working. If I create a new column there occurs an error:
if I try to create a measure, I´m not able to select the "_Vorauss. Baubeginn" column... Maybe I have to live with the day...🙈
Thanks to all for your help, I think I found a solution that works for me: I created a new calendar table which includes all the dates from my "_vorauss.Baubeginn" column. Within this table I created a new mmmm-yyyy column.
