Measure Needs Different Denominator for One Account

[CraigSchulz]

I have the following measure that calculates the average number of cars parked by noon on the same day of the week as the current day of the week. On a Friday, for any given account it will give me the average number for Fridays since the beginning of the year.

AvgParkedByNoon = CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday]))

This is working well, but one of the accounts is fairly new and still growing, so it doesn't make sense to look all the way to the beginning of the year for the denominator because there was no data early in the year. In fact I would like to only use the relevant days in July of this year for this particular account.

Can anyone help me with the code to do this?

Here is a link to a sample data file.

https://drive.google.com/file/d/1M2HOFq43UB7qduMqZw_EcBnQK_wecrN-/view?usp=sharing

- Craig

[v-juanli-msft]

Hi @CraigSchulz

Try this measure

Assume the particular account is "a", 

Month=MONTH('Calendar'[Date])

AvgParkedByNoon = IF([Area]="a",
CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday] &&[Month]=7)),
CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday]))

Best Regards

Maggie

[CraigSchulz]

This measure did work, once I changed the first lF to IF(VALUES(AccountAreaMaster[AcctAreaID]) = "130 - Hope Tower",

It broke my visual however, giving me the following error message:

Error Message:

MdxScript(Model) (7, 5) Calculation error in measure 'SMSYTD'[AvgParkedByNoon]: A table of multiple values was supplied where a single value was expected.

Perhaps this is because the visual starts out comparing Accounts ("average parked by noon" with "parked by noon today"), and then you can drill down to show AccountAreas with the same comparison. AcctArea "130 - Hope Tower" is one of two areas under AccountID "130", the other being AcctArea "130 - Main".

Is this the source of the error?

If not, what is?

Any thoughts on a solution?

- Craig

[v-juanli-msft]

Hi @CraigSchulz

VALUES function would return a table not a column or a scalar value, but the right of the "=" is a text value.

Best Regards

Maggie

[CraigSchulz]

Hey Maggie,

I'm not sure what to do following your response. I tried your recommended solution and it returned an error. Adding VALUES to the code seemed to work but the visual returned an error.

- Craig