Removefilters?? All??? AllSelected??? which to use?

[Anonymous]

Question:  How do I modify my measure to ignore the date slicer?

Details:

I have a cluster chart that captures the average number of tickets per week of the month for this month and last month.  I am also trying to add a line that captures the average number of tickets per week of the month for ALL months, but I can't seem to make it work.  I'm not sure which command function to use or where to apply it within the formula.

Here is the data that drives the bars above in table format since I can't upload anything to the cloud.  

As you can see from the chart above, because April is selected in the Date slicer, I'm getting the correct bar values but the line chart is simply using the selected month.  What I need is for the line chart to pick up these values (which are the weekly average counts for all months):

The measure to modify:

This is the formula that drives the above Weekly Avg I want:

_Inc SC Avg Count by W# = AVERAGEX ( VALUES( 'DATE Table'[_Date SC Week of Month (# only)] ), 'Incident Measures'[_Inc Count] ) / [_Count of Months]

The biggest hurdles I'm facing right now is that (1) all the parts of my above measure are also measures (2) that if I remove any of the date filters, the other parts of the formula don't work and (3) what I want to ignore is a slicer (I think, for example, that REMOVEFILTERS only works on actual filters, but I'm not sure).

[tamerj1]

@Anonymous 

Please try this

_Inc SC Avg Count by W# =
AVERAGEX (
    CALCULATETABLE (
        VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
        ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
    ),
    [_Inc Count]
) / [_Count of Months]

[Anonymous]

Nope.  I'm going to go do lunch now.  I'll be back in about an hour.  This whole thing is both fun and frustrating at the same time.

[tamerj1]

@Anonymous 

For me it's time to go sleep. However, please share code for the "count of month" measure. I think my first solution which resulted in large values can result in correct numbers if divided over the correct number of months (6). The first value for example (42.298) if divided over 6 will result in 7.05 which is the value you get when you select all the months (no selection in the slicer). 

[Anonymous]

I am still nowhere on this.  It really should be easier but I'm beginning to wonder if the functionality is broken in the April 2022 version because EVERYTHING I have seen has said that it should work.

As for Count of Month, I posted the formula in another answer but here it is again:

_Count of Months = COUNTROWS(VALUES('DATE Table'[Year Month Name]))

My current idea (but ran out of time today) was to create another little table with just the W1, W2, etc. column and the inc count / count of months formula and then relate it to my main either the date table or the source data table.

[tamerj1]

_Count of Months = COUNTROWS(ALL('DATE Table'[Year Month Name]))

[Anonymous]

@tamerj1 

I'm still completely stuck.  I'm going to re-post the issue and see if anyone else has any ideas.  heavy sigh.

[daXtreme]

This won't work?

[Meas] = 
CALCULATE(
    [_Inc SC Avg Count by W#],
    // just remove the filter
    // that comes from the slicer...
    ALL( Dates[Month] )
)

You just have to remove the filter from the value Apr-2022. Then, the measure above should see all the months and only the week that's on the x-axis.

[Anonymous]

That's what I thought too but the following doesn't work (where Year Month Name is the column in my date table that is the slicer):

_Inc SC Avg Count by W# 2 = CALCULATE('Incident Measures'[_Inc SC Avg Count by W#],ALL('DATE Table'[Year Month Name]))

[tamerj1]

Hi @Anonymous 
Please provide some sample data

[Anonymous]

The column headers run together but they are

Year Month Name

_Date SC Week of Month (W#)

_Inc Count

Year Month Name	_Date SC Week of Month (W#)	_Inc Count
Dec-21	W1	2,841
Dec-21	W2	7,503
Dec-21	W3	7,417
Dec-21	W4	5,226
Jan-22	W1	6,482
Jan-22	W2	7,896
Jan-22	W3	8,501
Jan-22	W4	10,396
Feb-22	W1	8,439
Feb-22	W2	8,636
Feb-22	W3	8,614
Feb-22	W4	8,199
Mar-22	W1	8,232
Mar-22	W2	8,166
Mar-22	W3	8,434
Mar-22	W4	13,457
Apr-22	W1	8,733
Apr-22	W2	8,028
Apr-22	W3	7,328
Apr-22	W4	9,620
May-22	W1	7,571
May-22	W2	6,758

The date table contains the following entry that drives the date slicer:

DATE Table =

var _fromYear=2020

var _toYear=YEAR(TODAY())

var _today=TODAY()

return

ADDCOLUMNS(

    CALENDAR(

                DATE(_fromYear,5,1),

                DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),

"Year Month Name",FORMAT([Date],"MMM-YYYY"),

)

[tamerj1]

Hi @Anonymous 
Please try

_Inc SC Avg Count by W# =
AVERAGEX (
    VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
    CALCULATE ( [_Inc Count], ALL ( 'DATE Table'[Year Month Name] ) )
) / [_Count of Months]

[Anonymous]

@tamerj1 

😕 It still returns the values from the month selected in the date slicer.

Please please please keep trying.

[tamerj1]

@Anonymous 
I think you have more columns in the date table than mentioned in the code. Howeve, Please try

_Inc SC Avg Count by W# =
AVERAGEX (
    VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
    CALCULATE (
        [_Inc Count],
        ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
    )
) / [_Count of Months]

[Anonymous]

That returned this:

Yes, my date table has many more columns and quite a few measures:

This is my master table with other relevant extra columns added at the bottom (I made it smaller to take up less space):

DATE Table =

var _fromYear=2020

var _toYear=YEAR(TODAY())

var _today=TODAY()

return

ADDCOLUMNS(

CALENDAR(

DATE(_fromYear,5,1),

DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))),

"Year",YEAR([Date]),

"Start of Year",DATE( YEAR([Date]),1,1),

"End of Year",DATE( YEAR([Date]),12,31),

"Month",MONTH([Date]),

"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),

"End of Month",EOMONTH([Date],0),

"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,

"Year Month Number",INT(FORMAT([Date],"YYYYMM")),

"Year Month Name",FORMAT([Date],"MMM-YYYY"),

"Date (dd-mmm-yy)",[Date],

"Day",DAY([Date]),

"Day Name Long",FORMAT([Date],"DDDD"),

"Day Name Short",FORMAT([Date],"DDD"),

"Day of Week",WEEKDAY([Date]),

"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,

"Month Name Long",FORMAT([Date],"MMMM"),

"Month Name Short",FORMAT([Date],"MMM"),

"Quarter",QUARTER([Date]),

"Quarter Name","Q"&FORMAT([Date],"Q-YYYY"),

"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),

"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),

"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),

"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),

"Week of Year",WEEKNUM([Date]),

"Start of Week", [Date]-WEEKDAY([Date],3),

"End of Week",[Date]-WEEKDAY([Date],3)+6,

"End of Week (dd-mmm-yy)",[Date]-WEEKDAY([Date],3)+6,

"Day Offset",DATEDIFF(_today,[Date],DAY),

"Month Offset",DATEDIFF(_today,[Date],MONTH),

"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),

"Year Offset",DATEDIFF(_today,[Date],YEAR)

)

Other key date columns and measures:

_Date SC Week of Month (W#) = IF(DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7) >= 4, "W4", "W" & DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7))

_Date SC Week of Month (# only) = IF(DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7) >= 4, 4, DIVIDE(CEILING(DAY('DATE Table'[Date]),7),7))

_Count of Months = COUNTROWS(VALUES('DATE Table'[Year Month Name]))

_Inc Count = IF (ISBLANK (DISTINCTCOUNT ('INCIDENTS'[INC_NUMBER])), BLANK(), DISTINCTCOUNT ('INCIDENTS'[INC_NUMBER]))

The column called "Week" is the  _Date SC Week of Month (W#)  column.

Count of Months and Inc Count are both measures.

[Anonymous]

@tamerj1 

For what it's worth, when I trigger the slashed circle on the table (with the date slicer selected), the results are what we want.  Unfortunately, we need that functionality to work on the measure itself.

[tamerj1]

@Anonymous 

There should be a solution. Please try

_Inc SC Avg Count by W# =
CALCULATE (
    AVERAGEX (
        VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
        [_Inc Count]
    ),
    ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
) / [_Count of Months]

[Anonymous]

There has to be a solution but so far, nada.  This produced the overall average for each week from the previous measure:

[tamerj1]

@Anonymous 

We will keep trying

_Inc SC Avg Count by W# =
AVERAGEX (
    VALUES ( 'DATE Table'[_Date SC Week of Month (# only)] ),
    CALCULATE (
        [_Inc Count],
        ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
    )
)
    / CALCULATE (
        [_Count of Months],
        ALLEXCEPT ( 'DATE Table', 'DATE Table'[_Date SC Week of Month (# only)] )
    )

[Anonymous]

@tamerj1 

THANK YOU FOR STAYING WITH THIS!!!!  😟 This time we got the daily average, I think:

[tamerj1]

@Anonymous 

Actually no this is not the daily average. This is the sum of the 6 months weekly averages divided by the total number of year-months in your date table which I can accurately calculate as 26 months.