topic Problem with Month-Year view in DAX Commands and Tips

Problem with Month-Year view

grasa — Fri, 06 Sep 2024 08:26:45 GMT

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.

Re: Problem with Month-Year view

123abc — Fri, 06 Sep 2024 08:43:29 GMT

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")

If you have any issue please feel free and contact with me.

Re: Problem with Month-Year view

grasa — Fri, 06 Sep 2024 09:02:11 GMT

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 😞...

Re: Problem with Month-Year view

123abc — Fri, 06 Sep 2024 09:20:02 GMT

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!

Re: Problem with Month-Year view

grasa — Fri, 06 Sep 2024 10:08:46 GMT

123abc Thanks again for your fast help 😊.

Unfortunately this is also not working, it says that the data for this visual isn´t found. Do you think this is because there are also blanks in the "_Vorauss. Baubeginn" column?

I could switch to Import Mode and I also tried that earlier but then there occures an error saying it is not possible because there are some blanks in some tables.

Re: Problem with Month-Year view

123abc — Fri, 06 Sep 2024 12:41:15 GMT

Yes, the issue you're facing could indeed be caused by the blanks in the "_Vorauss. Baubeginn" column. When there are null values, calculations like DATE(), YEAR(), and MONTH() might fail or return unexpected results.

You can modify your DAX formulas to handle blanks by using the IF and ISBLANK functions, ensuring that calculations only happen when there is valid data.

For example, you can wrap the DATE function to avoid calculations when the value is blank:

FirstOfMonth = IF(
ISBLANK([_Vorauss. Baubeginn]),
BLANK(),
DATE(YEAR([_Vorauss. Baubeginn]), MONTH([_Vorauss. Baubeginn]), 1)
)

This will return a blank value if "_Vorauss. Baubeginn" is blank, avoiding errors.

Replace Blanks with a Default Date

FirstOfMonth = IF(
ISBLANK([_Vorauss. Baubeginn]),
DATE(1900, 1, 1), // You can choose any default date here
DATE(YEAR([_Vorauss. Baubeginn]), MONTH([_Vorauss. Baubeginn]), 1)
)

This replaces the blank entries with a default date (e.g., January 1, 1900), ensuring no null values are present.

If the switch to Import Mode fails due to blanks in other tables, handling these blanks similarly—by either removing them or filling them with default values—can help resolve the issue.

Steps to Handle Blanks Before Import Mode:

Check All Tables for Blanks: Identify columns with blank values.
Fill Blanks: Either use DAX or Power Query transformations to fill those blanks with a default value.
Switch to Import Mode: After handling blanks, try switching again.

Let me know how this works for you!

Re: Problem with Month-Year view

Anonymous — Mon, 09 Sep 2024 03:15:40 GMT

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 Xu

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

Re: Problem with Month-Year view

grasa — Mon, 23 Sep 2024 08:25:17 GMT

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...🙈

Re: Problem with Month-Year view

grasa — Mon, 23 Sep 2024 09:21:56 GMT

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.