Date Filter not working with CALCULATE, FILTER, then ALLSELECTED

[DuranRoberts]

Description:

I need help trying to figureout my issue with a visual not displaying after I use a date filter. Below is my simple measure for counting the unique ID while filtering ALLSELECTED within the given Year/Month (202111). My result is a running total that doesn't care about the year so that I can keep it adding for any given time range, instead of it splitting per year (which is the default calculation). I can't use ALL in my FILTER because none of the page filters will function.Blank visual when filtering CreatedDt only.

Problem:

My problem is that when I have a change my date range filter (CreatedDt Filter) everything in my visual goes blank, but when I use any other filter (i.e. Carrier, Agent, etc.) those will work for filtering. It seems that its only the date filter and when the measure has ALLSELECTED in it. 

Measure Calculation:

QuoteRequestId running total in CreatedDtMonth =

CALCULATE ( COUNT( 'vw_rpt_AllSubmissions'[QuoteRequestId] ),

FILTER ( ALLSELECTED ( 'vw_rpt_AllSubmissions' ), 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] <= MAX ( 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] ) ) )

[DuranRoberts · Accepted solution]

I have actually found my own solution to this issue, see below for my answer:

Calculation Solution:

CALCULATE ( COUNT( 'vw_rpt_AllSubmissions'[QuoteRequestId] ), FILTER ( ALLEXCEPT( vw_rpt_AllSubmissions,vw_rpt_AllSubmissions[PayToCarrierName],vw_rpt_AllSubmissions[AgencyName], vw_rpt_AllSubmissions[AgentFullName],vw_rpt_AllSubmissions[CreatedDt] ), 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] <= MAX ( 'vw_rpt_AllSubmissions'[CreatedDtYearMonth] ) ) )

Solution Description:

I have come up with the above work around that allows me to add the listed Page Filters into my DAX calculated column so that it manually accepts what is being used. This isn't the best case scenario because I'll have to add it each time that I change my page filters but I won't be doing that often. I hope someone else can benefit from this solution and please leave any comments if a better solution comes up.

[amitchandak]

@DuranRoberts ,

Try running total with date table. example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[DuranRoberts]

Hello @amitchandak,

I appreciate your feedback and response, but this was the calculation I used to get where I'm at. The ALLSELECTED is what wasn't working with my CreatedDt (Date) filter. I have since figured out my work around for this problem that seens to be working for now. I just needed to use the ALLEXCEPT function and list my page filters that I'm using. I'll submit it as my answer shortly and thanks again for your help, hopefully others will find this useful as well.

Thanks,

Duran