DAX function / filter context issue

[gbrunnerbi]

Hi,

I need some help with filter contexts and dax formulas.

My setup is this:

I have a EndQuarter - ENDDATE - selector (slicer) (e.g. 9/30/2019) - which filters all values in my data table - this is selected by the user on the canvas (see Screenshot 1 or 2)

I want to sum up all values in a certain column from a certain start date - STARTDATE - which I store in a measure, e.g. 10/1/2018 this is dynamic) until the selected date on the canvas (by the slicer)

meaning the sum from e.g. 10/1/2018 until what the user selects but let us say 9/30/2019

This is my measure:

ResultMeasure =
VAR Result =
CALCULATE (
SUM ( 'table'[COL10] ),
KEEPFILTERS ( 'table'[DDIM01GRP28] = "1" ),
KEEPFILTERS ( NOT 'table'[DDIM01GRP23]
IN {
"211111",
"211112",
"211113",
"211114",
"211117",
"211118",
"211119",
"211120",
"211121",
"211122",
"211123",
"211131",
"211132",
"211133",
"211134"
} ),
KEEPFILTERS ( NOT 'table'[DDIM01GRP25] IN { "2112", "2113" } ),
KEEPFILTERS ( NOT 'table'[DDIM01GRP26] IN { "212", "213", "221", "222" } ),
KEEPFILTERS ( 'table'[DDIM01GRP27] IN { "11", "12", "21" } ),
DATESBETWEEN ( Dates[Date], [FirstDateMeasure], [LastDateMeasure] )
)
RETURN
Result

It does everything as imagined, I get the correct amount.

What I struggle with however, is showing this amount over time in a chart ending at the date selected in the slicer.

When I have the drilldown of the slicer (EndofQuarter) on the chart - it is only showing me the 3 months in the quarter because it filters the quarter that I selected (with correct values) - I tried to break out of this with REMOVEFILTERS or ALL but it did not work since the sums would end up wrong.

Screenshot1:

Screenshot 1 - filter on

When I turn off the interaction in the slicer it gives me all the data, also correctly calculated but it does not end at the EndofQuarter as selected (9/30/2019) but rather at the last date in the dataset (in 2020 - which makes sense of course) - and I cannot use the value of the EndofQuarter slicer since it does not exist within the filter context?

Screenshot2:

Screenshot 2 - filter off

I want to end up with a visual like this (ends at the Quarter/Date I selected) but still calculates the amount correctly - summing up all values from a certain date until date in the filter context (every month basically)

Screenshot3

Any ideas?

[gbrunnerbi · Accepted solution]

I was just able to actually solve this:

-I created a new table (basically duplicating one of my queries)

-Added a column calculating the values that I need (you have to adapt it since it is not a measure of course, like removing aggregators and replacing them with the actual columns in your table)

Now I created a measure which should be displayed in my visual.

e.g. CALCULATE ( SUM ('table'[Value))

I used REMOVEFILTERS to get rid of the slicer selection (this selection forced me to stay within the quarter which the user selected)

And now I could use DATESBETWEEN('Dates'[Date] , FIRSTDATE('Table'[Date]) , LASTDATE('Dates'[Date] )

to actually get the time period that I wanted to look at, in my visual.

Now whatever quarter the user selects will be the last data point on my visual.

If someone has a similar problem and my solution is not clear, please pm me

[v-zhenbw-msft]

Hi @gbrunnerbi ,

We can use the following steps to meet your requirement.

1. The line chart date based on table date, but the slicer date based on date table date.

There is no relationship between table and date table.

2. Then we can create a measure to get the line chart value before Endofmonth. The result like this,

Measure = IF(SELECTEDVALUE(Dates[Date]),CALCULATE(SUM('Table'[result]),FILTER('Table','Table'[date]<=SELECTEDVALUE(Dates[Date]))),CALCULATE(SUM('Table'[result])))

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbrunnerbi]

I need to keep the filters in place (they are filtering my data table)

AND

I cannot use a disconnected table but rather have to solve it with a DAX formula.

It is not that straight forward...

[amitchandak]

@gbrunnerbi , you using the date filter from the date where you are trying to remove, that is what I understood.

Try filter like

filter(all(Date), Dates[Date]>=[FirstDateMeasure] && Dates[Date]<= [LastDateMeasure] )

Or use another date to filter

Check https://www.youtube.com/watch?v=duMSovyosXE

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[gbrunnerbi]

Thanks but if I use your formula I get the wrong amounts, it is not summing up the values correctly

I think there is an inherent difference between FILTER and DATESBETWEEN

I am trying something like using ALL(DATE) as the table for my DATESBETWEEN function which does not work though I think?

I want to break out of the filter context within the measure (like with removefilters) and then still being able to use DATESBETWEEN but that is not possible so far?