Forum Discussion
Calculated column do not react on page filter
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!
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 _filterRevenue = 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 _result4. 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.
Best Regards,
changqing
2 Replies
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 _filterRevenue = 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 _result4. 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.
Best Regards,
changqingLouke , 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") )
