Calc for Average sales per day from current month VS Average sales from same month last year

[unclejemima]

I've used the averagex function to get average per day, and I can then add a slicer to the report to dynamically change the average to get the average sales per day for say the current month or the previous month.

But what I'm trying to do is have a gauge with the average sales per day for current month to be the value, and the average sales for the same month last year as the target.  There are other variables on the report, so I have to hard code the calculation for current month daily average and same month last year daily average so I can use them independent in the gauge.

I'm hoping I can simply modify the AverageX formula that I currently have...but I don't know how.

Here is my Averagex formula:

AverageSalesPerDay = AVERAGEX( VALUES( Calendar[Date] ),[Income4000'sCalc])

Looking forward to the replies, and many thanks!

[Greg_Deckler]

Check out my Time Intelligence The Hard Way Quick Measure here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Seems like it might apply to your situation. Otherwise, sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[unclejemima]

Thanks for your reply.

I was hoping my question was no to complicated...I'd like to upload my dataset but due to the amount of senstive data my company won't allow it.

I'll try to keep it simple...I have a calender table with date, year, month, week, quarter, is last month, is current month, day in week sort, month in year sort, and many many more calendar specific columns that should assist my calculation.

I was hoping to have my calulation look like so...

if('Calendar'[IsLastMonth]="yes",AVERAGEX( VALUES( Calendar[Date] ),[Income4000'sCalc]),blank())

But I can't seem to use an IF statment with a measure, not will it accept the table Calendar[IsLastMonth] as its a column, not a measure.

Can I do a measure based off a forced date range of "IsLastMonth"=Yes for a measure or is this not possible?

[libratic · Accepted solution]

Does this work?

CALCULATE(
	[AverageSalesPerDay],
		'Calendar'[IsLastMonth] = "yes")

[unclejemima]

Yes it does 🙂  marked as answer thank you!!

[unclejemima]

When I use the formula I get this error...

A single value for column 'IsLastMonth' in table 'Calendar' 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.

[Greg_Deckler]

So, the reason that is not working is that in Measures when you refer to a column, you have to use an aggregation like MAX, MIN, SUM, COUNT, etc. MAX or MIN might work to essentially "wrap" your column reference in but it difficult to say without knowing how the data is organized and the context in which you are using the measure.

You could also try a calculated column, but, again, difficult to say with specificity. Often when dealing with sensitive data it is advantageous to mock up some sample data that simulates your circumstance. Generally you do not need a lot of that, just maybe 10 rows with only the columns involved that are relevant.