Calculated column do not react on page filter

[Louke]

Hello all,

I'm trying to create a calculated column that groups the diff of each rows due date and a variable deadline (page filter).

I have a data set like this:

invoice number	due date	customer	revenue
500001	11.06.2022	Tom	100,00 €
500002	11.06.2022	Jack	50,00 €
500003	17.06.2022	Isabell	-50,00 €
500004	18.06.2022	Tom	-250,00 €
500005	26.06.2022	Harry	50,00 €
500006	30.06.2022	Jack	100,00 €
500007	30.06.2022	Frank	-200,00 €
500008	30.06.2022	Tom	150,00 €
500009	03.07.2022	Isabell	50,00 €

I need a variable page filter that could be switched by the user:

Now, I would like to add a calculated column for the datediff between due date and deadline date (in the example I set 31.07.2022 - but it should be possible to change it):

invoice number	due date	customer	revenue	datediff
500001	11.06.2022	Tom	100,00 €	-50
500002	11.06.2022	Jack	50,00 €	-50
500003	17.06.2022	Isabell	-50,00 €	-44
500004	18.06.2022	Tom	250,00 €	-43
500005	26.06.2022	Harry	50,00 €	-35
500006	30.06.2022	Jack	100,00 €	-31
500007	30.06.2022	Frank	200,00 €	-31
500008	30.06.2022	Tom	150,00 €	-31
500009	03.07.2022	Isabell	50,00 €	-28

Result:

datediff	revenue
> 40 days	350,00 €
< 40 days	550,00 €

As I already read, page filters can not affect context in calculated columns. But is there a way I can keep the variable deadline filter and achieve the desired result?

I already tried different ways like:

datediff =
IF( DATEDIFF(MIN(Time[Deadline]), Table[due date], DAY) < -40, 
"> 40 days", 
"< 40 days")

Unfortunately PBI ignores my page filter for "Deadline".

Thank you so much in advance!

[changqing · Accepted solution]

Hi @Louke ,

My steps are as follows:

1. Create a calendar table with all the dates needed and apply it as field of the slicer. Select Before for the slicer type.

2. Enter data -> Table2:

3. New measures:

filter = 
VAR _deadline =
    MAX ( 'Calendar'[Date] )
VAR _days =
    DATEDIFF ( MAX ( 'Table'[due date] ), _deadline, DAY )
VAR _filter =
    IF ( _days <= 40, 1, 0 )
RETURN
    _filter

Revenue = 
VAR _less_than_40 =
    SUMX ( FILTER ( 'Table', [filter] = 1 ), 'Table'[revenue] )
VAR _more_than_40 =
    SUMX ( FILTER ( 'Table', [filter] = 0 ), 'Table'[revenue] )
VAR _result =
    SWITCH (
        SELECTEDVALUE ( 'Table2'[datediff] ),
        "< 40 days", _less_than_40,
        "> 40 days", _more_than_40
    )
RETURN
    _result

4. Result:

You can change the values in the slicer at any time and the table visual will change dynamically. The PBIX file is attached for reference.

PBIX file 

Best Regards,
changqing

[amitchandak]

@Louke , the Calculated column, does not react to slicer or filter. Only measure can.

Create a measure like

datediff = Maxx( Table,
IF( DATEDIFF(MIN(Time[Deadline]), Table[due date], DAY) < -40,
"> 40 days",
"< 40 days") )