YTD average by Day of Week

[joshcomputer1]

Hi there,

I am wanting to get an average for the day of the week, YTD.  The data is situated like the chart on the left. I want the chart on the right. Then, I have a date filter on the page too, so the user selects a date and it filters that date. I need it to not be filtered by the date filter.  I think I can turn off the edit interactions with the filter. If I can do this with just a measure, that would be ideal.  I do have a date table named date[date]. 

This is what I have so far, but it doesn't have the YTD averaged in it.

AVG CAlls By DOW = calculate(average(table1[Count]), groupby(table1, table1[DOW]))

[v-danhe-msft · Accepted solution]

Hi @joshcomputer1,

Based on my test, you could refer to below formula:

Measure = CALCULATE(AVERAGE(Data[Count]),FILTER(ALL('Data'),'Data'[DOW]=MAX('Data'[DOW])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Ashish_Mathur]

Hi,

Try this

1. In a Table visual, drag DoW in the Row labels
2. Write this measure

=CALCULATE(AVERAGE(Data[Count]),ALL(Data[Date]))

Hope this helps.

[joshcomputer1]

I cannot do the DOW as the legend in my setup. The user will select a date and the card needs to pick up the DOW from the date selected, then give the average for that day YTD. When I use that measure (see the card on the right), what it shows is the average for the date selected in the slicer. I think the DAX needs to include something with DOW.  

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(AVERAGE(Data[Count]),DATESBETWEEN(Calendar[Date],DATE(2018,1,1),MAX(Calendar[Date])),FILTER(Data,Data[Dow]=MAX(Data[DoW])))

There should be a Calendar Table with a relatiosnhip from the Date column of the Data Table to the Date column of the Calendar Table.

[joshcomputer1]

This is close.  The measure shows the average count by DOW, however, The date filter is still filtering date and not just DOW. Is there a way to adjust this so that when a date is selected, the DOW is filtered, but date is not?

**I do have a calendar table named datetable[date] and it has a relationship to the data table. 

=CALCULATE(AVERAGE(Data[Count]),DATESBETWEEN(Calendar[Date],DATE(2018,1,1),MAX(Calendar[Date])),FILTER(Data,Data[Dow]=MAX(Data[DoW])))

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  In that file, please select any one date and your expected answer.  When i write my DAX formula, i will compare my result with yours.