Forum Discussion
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!
Anonymous
Please see the image on how I did it. The column in the formula should in in date format for this to work.
7 Replies
- VasTg
Memorable Member
Anonymous
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
- AnonymousNot 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.
Thanks!
- AnonymousNot 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.