Ignore slicers and add separate filter

[Anonymous]

Hi everybody.

I have a particularly hard problem, that i just can't solve myself.

I have a bunch of sales in my dataset that i use dateslicers to filter. That works like a charm. But for one of my measures i want it to ignore the lower dateslicer in my daterange. So my measure takes all sales from the beginning of my data until the last date set by my daterange slicer. I've gotten close, but when i use ALL, ALLEXCEPT etc. I can't get it to accept my new filter (makes sense).

I'd be happy to clarify further if needed. 🙂

[MFelix]

Hi @Anonymous ,

You need to make a measure similar to this:

Measure ignoring Filter =
CALCULATE (
    SUM ( Table[Column] );
    FILTER ( ALL ( Table[Date] ); Table[Date] <= MAX ( [Table[Date] ) )
)

What is happening in this formula is that we are checking the date selection on your slicer and then picking up all the values that are lower than the maximum date this way excluding minimum date.

Regards,

MFelix

[Anonymous]

Thanks for the reply @MFelix 

That still doesn't work.

In the coming pictures i have used your calculation on the column named "Kredit Balance".

The other column named "Kredit Bevægelse" is just a sum of my sales amount. You can see my calculation here.

Finansposter[Posting Date is the date the sale was posted, and Dato[Date] is my datetable. 

Finansposter[Amount] is the amount of the sale ofcourse. 

In the first photo there is no filter on the dates. The whole daterange is accounted for.

It shows the same number as it should.

 

Then if i move the max of my daterange and slice the top it still shows the same number, as it should.

But! When i move the lowest date of my daterange and slice it, i would like the column "Kredit Balance" to have the same number as in the previous photo, but it still changes.

[MFelix]

Hi @Anonymous ,

Are the columns Finansposter[Posting Date] and Dato[Date] related with each other or are this two separate tables without any relationship?

Regards,

MFelix

[Anonymous]

Hi @MFelix ,

The two tables are related.

[MFelix]

Hi @Anonymous ,

Your measure should be redone to this:

Kredit Balance = CALCULATE(
SUM(Finansposter[Amount]);
FILTER (ALL(Dato[Date]); Dato[Date] <= MAX(Dato[Date]) )

)

I'm assuming you slicer is based on the table Dato.

Regards,

MFelix