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!
Solved! Go to Solution.
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
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.
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.
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
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?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!