cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SC-NDOW
Regular Visitor

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

Accepted Solutions
Super User I
Super User I

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

@SC-NDOW 

 

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

View solution in original post

7 REPLIES 7
Super User I
Super User I

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

@SC-NDOW 

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

SC-NDOW
Regular Visitor

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

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!

SC-NDOW
Regular Visitor

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

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

Super User I
Super User I

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

@SC-NDOW 

 

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

SC-NDOW
Regular Visitor

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

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?

Super User I
Super User I

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

@SC-NDOW 

 

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

View solution in original post

SC-NDOW
Regular Visitor

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

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

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors