cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
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."

 

PowerBIError.png

 

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!

1 ACCEPTED SOLUTION

@Anonymous 

 

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

 

image.png

Connect on LinkedIn

View solution in original post

7 REPLIES 7
VasTg
Memorable Member
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

Connect on LinkedIn
Anonymous
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.

 

PowerBIError2.pngThanks!

Anonymous
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

Anonymous
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:

Date.MonthName([created_at_PST])

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

 

@Anonymous 

 

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
Anonymous
Not applicable

Thanks, I clicked on error and it shows:

"Expression.Error: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=reversed_at_PST"

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?

@Anonymous 

 

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

 

image.png

Connect on LinkedIn

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.