DAX Help for multiple layer filtering

[pmoore]

Hello,

I've been trying to find an answer for a couple of days but still don't have a good enough grasp on DAX to get the formula to work.  So, I'm trying to display in a table that has the last 30 days, an average for each day of the week, filtered by a dynamic date range.  So, for example, my last 30 days would be 3/5-4/3/2018.  For each day, I have a distinct total of encounters.  I want to compare that number to an average for the specific Day of the Week (so, for 4/3, an average for Tuesdays).  However, I want to limit the range of dates evaluated for the average to let's say between TODAY()-60 to TODAY()-30.  Also important to note that I don't want to lose any filters on the page (for example, Practice/Facility).

The table I'm using is the following: DateKey, MonthName, DayofMonth, DayName, Year, then the calculations that I need.  

I've got the formula to work for just diplaying the total of all days for that Day of the week (I can figure out the average from there):

Count Enc Total Day of Week = CALCULATE(DISTINCTCOUNT('ChargeDetailfact'[EncID]), ALLEXCEPT('Date Of Service','Date Of Service'[DayName])).  I also have measures that are used elsewhere that calculate the totals based on the range but those don't work in this table as they repeat the total all the way down.  I'm struggling to add the filter of the date range into the same formula I mentioned above, without losing ALLEXCEPT filter.

Any help would be appreciated!

Thanks,

[Greg_Deckler]

Sample/example data please. And also what the expected output from that sample data would be.

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

[pmoore]

Here's some sample data from Date table and the Fact Table and the desired result.  I'm using a simplified result where I want to see 10 previous days but the formula I'm looking for would calculate the total and average (distinct count of Encounter) based on the time period of Today -15 days to Today -5 days.  I included EntityID and LocationID as indications of filters used on the report but used LocationID 2 for my results. 

Just a note that the data would actually have much older dates of service as well and if I use ALL filter (enumerating columns without DayName), I get the average for that day back to the beginning of time.  However, the goal is to calculate the average for each specific Day of the week, within a certain date range and that range being different than what's included on the visual.

There is a many to one relationship:

ChargeDetailfact to Date

ChargeDetailfact to Location

ChargeDetailfact to Entity

Result actually displayed on the report:

Date	DayofMonth	Year	DateKey	DayName	Distinct Count of Enc	Total Today-15 to Today-5	Avg Today-15 to Today-5
4/4/2018	4	2018	20180404	Wednesday	1	6	3
4/3/2018	3	2018	20180403	Tuesday	2	4	2
4/2/2018	2	2018	20180402	Monday	3	1	1
4/1/2018	1	2018	20180401	Sunday	1	5	5
3/31/2018	31	2018	20180331	Saturday		2	2
3/30/2018	30	2018	20180330	Friday	1	0	0
3/29/2018	29	2018	20180329	Thursday	1	5	2.5
3/28/2018	28	2018	20180328	Wednesday	4	6	3
3/27/2018	27	2018	20180327	Tuesday	1	4	2
3/26/2018	26	2018	20180326	Monday	1	1	1

Full set of results if the filter on the report didn't limit the visual to the last 10 days (Goes out to Today -15 days):

Date	DayofMonth	Year	DateKey	DayName	Distinct Count of Enc	Total Today-15 to Today-5	Avg Today-15 to Today-5
4/4/2018	4	2018	20180404	Wednesday	1	6	3
4/3/2018	3	2018	20180403	Tuesday	2	4	2
4/2/2018	2	2018	20180402	Monday	3	1	1
4/1/2018	1	2018	20180401	Sunday	1	5	5
3/31/2018	31	2018	20180331	Saturday		2	2
3/30/2018	30	2018	20180330	Friday	1	0	0
3/29/2018	29	2018	20180329	Thursday	1	5	2.5
3/28/2018	28	2018	20180328	Wednesday	4	6	3
3/27/2018	27	2018	20180327	Tuesday	1	4	2
3/26/2018	26	2018	20180326	Monday	1	1	1
3/25/2018	25	2018	20180325	Sunday	5	5	5
3/24/2018	24	2018	20180324	Saturday	2	2	2
3/23/2018	23	2018	20180323	Friday		0	0
3/22/2018	22	2018	20180322	Thursday	4	5	2.5
3/21/2018	21	2018	20180321	Wednesday	2	6	3
3/20/2018	20	2018	20180320	Tuesday	3	4	2

Fact Table (ChargeDetailfact):

pk_Detail	DateOfSvc	EncID	EntityID	LocationID
1	20180320	1	2	1
1	20180320	2	2	1
1	20180320	3	2	2
1	20180320	3	2	2
1	20180320	4	2	2
1	20180320	5	2	2
1	20180321	6	2	1
1	20180321	7	2	2
1	20180321	8	2	2
1	20180322	9	2	2
1	20180322	10	2	2
1	20180322	11	2	2
1	20180322	12	2	2
1	20180323	13	2	1
1	20180323	14	2	1
1	20180324	15	2	2
1	20180324	16	2	2
1	20180324	15	2	2
1	20180324	16	2	2
1	20180325	17	2	1
1	20180325	18	2	2
1	20180325	18	2	2
1	20180325	19	2	2
1	20180325	20	2	2
1	20180325	21	2	2
1	20180325	22	2	2
1	20180326	23	2	2
1	20180326	23	2	2
1	20180327	24	2	2
1	20180328	25	2	1
1	20180328	26	2	1
1	20180328	27	2	2
1	20180328	28	2	2
1	20180328	29	2	2
1	20180328	30	2	2
1	20180329	31	2	2
1	20180329	31	2	2
1	20180329	31	2	1
1	20180330	32	2	1
1	20180330	32	2	2
1	20180331	33	2	1
1	20180331	34	2	1
1	20180401	35	2	1
1	20180401	35	2	1
1	20180401	36	2	2
1	20180401	36	2	2
1	20180402	37	2	2
1	20180402	38	2	2
1	20180402	39	2	2
1	20180403	40	2	1
1	20180403	41	2	2
1	20180403	42	2	2
1	20180404	43	2	2

Date Table:

DateKey	Date	DayofMonth	Year	Month	DayName
20180320	3/20/2018	20	2018	03	Tuesday
20180321	3/21/2018	21	2018	03	Wednesday
20180322	3/22/2018	22	2018	03	Thursday
20180323	3/23/2018	23	2018	03	Friday
20180324	3/24/2018	24	2018	03	Saturday
20180325	3/25/2018	25	2018	03	Sunday
20180326	3/26/2018	26	2018	03	Monday
20180327	3/27/2018	27	2018	03	Tuesday
20180328	3/28/2018	28	2018	03	Wednesday
20180329	3/29/2018	29	2018	03	Thursday
20180330	3/30/2018	30	2018	03	Friday
20180331	3/31/2018	31	2018	03	Saturday
20180401	4/1/2018	1	2018	04	Sunday
20180402	4/2/2018	2	2018	04	Monday
20180403	4/3/2018	3	2018	04	Tuesday
20180404	4/4/2018	4	2018	04	Wednesday