Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Not applicable

Using Measure to create month/year calculation, works in one query but not the other

I'm trying to create a Month & Year calculation and thought I was suppose to use Measure. I have two different queries one for Sales transactions and another for Reversals transactions. Forgive me as I’m a newbie and learning Power BI:

Oddly, in my sales query it works fine but in my reversal query, it gives me this error

"A single value for variaton 'Month' for column 'reversed_at_PST' in table 'All_AMS_Reversals' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."




This is the formula I’m trying to enter for my reversals table report:

Month & Year Reversals = 'All_AMS_Reversals'[reversed_at_PST].[Month] & " " & 'All_AMS_Reversals'[reversed_at_PST].[Year]

Whereas the one in the sales table report works fine:

Month & Year Sales = 'All_AMS_Sales'[created_at_PST].[Month] & " " & 'All_AMS_Sales'[created_at_PST].[Year]

When I try to modify or edit the Measure formula, it doesn’t autopopulate with all my available fields, it only shows two? I think this might be where the source of my problem is starting? I should have a long list of fields to select from?


I confirmed they are both data type: date/time and the 2nd query is basically a copy of the 1st query so it should have all similar settings. I have picked the year and month in my hierarchy under fields. I’m not sure what I’m missing and why it works in sales but not the other?


Thanks so much!




Please see the image on how I did it. The column in the formula should in in date format for this to work.



Connect on LinkedIn

View solution in original post

Memorable Member
Memorable Member


Are you creating a calculated column? It doesn't look like a measure unless you are concatenating two measures. 


Are you creating that in a right table?


Edit: You should wrap them with one of the aggregated function.


MIN( 'All_AMS_Reversals'[reversed_at_PST].[Month]) & " " & MIN('All_AMS_Reversals'[reversed_at_PST].[Year])


Maybe you want to create "New Column" instead of "New Measure"?


If it helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn
Not applicable

Yes I'm creating a measure and referencing a date column to get month as well as year from it. I am selecting the right table. What is confusing is that it works in one table but not the other? Also, why is when I'm in the data entry stage, I don't see list of all my available fields? It only shows 3 fields when I have 15 fields? It worked fine when I built the first one.



Not applicable

I tried making a custom column with basic just MonthName, it says no syntax errors but you can see upon saving it, the column is showing ERROR. What am I missing, I confirmed the referenced field is date/time data type. PowerBIError3.png

Not applicable

I found the issue, the syntax was not correct even though the custom column dialog box says no errors detected. I was missing the parenthesis in the formula. The correct formula is in my posting #4 is:


Once I fixed it, everything works now. 😊 Thanks VasTg, your final posting helped me get what I need and it's all working, I knew I was missing something simple:

Date.MonthName([created_at_PST]) & " " & Text.From(Date.Year([created_at_PST]))




Click on the cell where it shows error to see the actual error.


Are you trying to append month and Year?What is the final requirement

Connect on LinkedIn
Not applicable

Thanks, I clicked on error and it shows:

"Expression.Error: We cannot apply field access to the type Function.

Yes, my final requirement is to create a concationated field showing "Month & Year" . For example: if my field: reversed_at_PST has a date of 1/4/20 then the result of the formulate would show: "January 2020".

As I mentioned, it working in my first query/table but not in the 2nd one. Is there something wonky I have on that specific date field that is preventing this?



Please see the image on how I did it. The column in the formula should in in date format for this to work.



Connect on LinkedIn

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.