Calculated Measure Needs Different Denominator for One of 24 Accounts

[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, say for AccountID 130.

Can anyone help me with the code to do this?

- Craig

[v-jiascu-msft]

Hi Craig,

You can upload your file to the cloud drive like OneDrive, GoogleDrive then share the download link here.

Best Regards,

Dale

[CraigSchulz]

I'm afraid I don't feel comfortable sharing the dataset and this is for a client project and I am not sure they would authorize sharing information about the company on a public forum. Thanks for offering your help, but I guess I'll have to figure it out on my own.

- Craig

[RMDNA]

@CraigSchulz,

Any data works, even if it's fabricated. We just need to see the structure and expected result.

[CraigSchulz]

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

Above is a link to a sample data file. In Power BI this file is linked to my calendar table. File shows several accounts and all the cars parked at those accounts by date, and whether the cars were parked by noon. I have another file that shows cars parked by noon today. I want a visual that compares, by account, cars parked by noon today to average cars parked by noon YTD for the same day of the week. One of the accounts, "New Tower", is new, so average cars parked YTD would be bogus since it didn't start operating until June. For that particular account I would like the denominator of the average calculation to use only dates for the month of July 2018, not the full year. For the rest of the accounts dates for the full year are fine. How can I accomplish this?

- Craig

[v-jiascu-msft · Accepted solution]

Hi Craig,

It should work if the relationship between the two tables is Both filter direction. As we can see from the snapshot below, the Measure 2 returns the weeks. 

Best Regards,

Dale

[Ashish_Mathur]

Hi,

Could you share a dataset and show the expected result.

[CraigSchulz]

Not sure how to share a dataset on this platform.