Custom table is not working when filtering on date

[Jack00711]

Hello Power BI Community,

I am having issue with a report, I am trying to create.

I have 2 tables Calendar and Tasks.
Below is the table Tasks :
Deliverable_IDTaskValueDate

1	A	0.8	30/12/2024
1	B	0.7	9/12/2024
1	C	0.6	9/12/2024
1	D	0.5	9/12/2024
2	A	0.8	9/12/2024
2	B	0.7	9/12/2024
2	C	0.6	1/12/2024
2	D	0.5	2/12/2024
1	A	0.5	3/12/2024
1	B	0.4	4/12/2024
1	C	0.3	5/12/2024
1	D	0.2	6/12/2024

I am creating a custom table using the below Formula :

FilteredTasks =

VAR SelectedDate = MAX('Calendar'[Date]) 

RETURN

    FILTER(

        ALL(Tasks),

        Tasks[Date] <= SelectedDate 

    )

I want to use the Calendar Date as a Filter. When I select Date 6/12/2024, It should show all the tasks which have Date less than 6/12/2024.

I have checcked data type in both tables. I have attached the PBIX file and excel used in the report.

I would really apprieciate if somebody can help.

Thank you

[grazitti_sapna]

Hi @Jack00711 ,
If you want to dynamically filter the data based on the slicer, you should not use a calculated table like FilteredTasks, as it does not respond dynamically to slicers. Instead, you can achieve the desired behavior in one of the following ways:

Option 1: Add a Measure
You can create a measure to use directly in your visual:

FilteredTaskValue = 
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
SUMX(
FILTER(
Tasks,
Tasks[Date] <= SelectedDate
),
Tasks[Value]
)

You can then add this measure to your table or matrix visual, and it will dynamically display the sum of Value for tasks with dates less than or equal to the selected date.

Option 2: Use a Visual-Level Filter
If you prefer using a calculated column or table, try applying a visual-level filter instead of relying on a calculated table. For example:

1. Use the original Tasks table for your visual.
2. Add a slicer with Calendar[Date].
3. In the table or matrix visual, set a filter on Tasks[Date]:
   • Condition: Tasks[Date] <= SelectedDate.

Option 3: Modify the DAX for Calculated Table
If you insist on creating a calculated table, you can modify the DAX formula as follows:

FilteredTasks =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
FILTER(
Tasks,
Tasks[Date] <= SelectedDate
)

Then, use this table in your visuals. Note that the calculated table is static and does not respond to slicers unless you refresh the model.

I hope the provided solution works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[Sergii24 · Accepted solution]

Hi @Jack00711, you don't need to build a calculated table for this case. Calculated table is refreshed once, when your semantic model is refreshed. Therefore, any further interaction with slicers won't change it.

If you already have a classic data model like this, the only thing you need to do is to apply a filter on "Date" column from "Calendar" table, it will filter the fact table accordingly.

Then you create DAX measure that will show you data for all fields selected before the current date, but when using data in slicer visual there is an easier way:

You can find sample pbix attached.

Good luck with your project 🙂

[danextian]

Calculated tables are update only when:

-the formula is modifed

-the reference tables/columns update

-upon refresh

It isn't directly updated by slicer selections. Changes in the table view can affect the report view but it is no the other way around.

[Bibiano_Geraldo]

Hi @Jack00711 ,
Calculated tables (like the FilteredTasks table you're creating) are not directly affected by slicers in Power BI because calculated tables are evaluated during the data refresh and not dynamically like measures or visuals. This means slicers won't affect them in real-time.

Here’s how you can address the problem using a measure that will react to slicers:

FilteredTasksMeasure =
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
    CALCULATE(
        COUNTROWS(Tasks),
        FILTER(
            Tasks,
            Tasks[Date] <= SelectedDate
        )
    )

You can now use this measure in your visuals, and the results will be filtered by the selected date from the slicer in the Calendar table. It will dynamically show the number of tasks (or other aggregations) based on the selected date.

[Jack00711]

Thank you @danextian  @Bibiano_Geraldo @Sergii24 
What I am trying to achieve is this : 

For Selected Date 9/12/2024:

Deliverable_ID Task Value Date

1	A	80%	9/12/2024
1	B	70%	9/12/2024
1	C	60%	9/12/2024
1	D	50%	9/12/2024
2	A	80%	9/12/2024
2	B	70%	9/12/2024
2	C	60%	1/12/2024
2	D	50%	2/12/2024

For Selected Date 6/12/2024:

Deliverable_ID Task Value Date

2	C	60%	1/12/2024
2	D	50%	2/12/2024
1	A	50%	3/12/2024
1	B	40%	4/12/2024
1	C	30%	5/12/2024
1	D	20%	6/12/2024

I am using the following formula but it's not working properly. This formula I was trying to debug where the issue is, that's where that table I created.

Value per Task =

VAR SelectedDate = MAX('Date'[Date]) -- Get the selected date from the Date table

VAR FilteredTasks =

    FILTER(

        Tasks,

        Tasks[Date] <= SelectedDate -- Include only tasks with dates <= selected date

    )

VAR LatestDates =

    SUMMARIZE(

        FilteredTasks,

        Tasks[Deliverable_ID],

        Tasks[Task],

        "LatestDate", MAX(Tasks[Date]) -- Get the latest date for each Deliverable_ID and Task

    )

RETURN

    CALCULATE(

        MAX(Tasks[Value]),

        FILTER(

            Tasks,

            Tasks[Date] =

                MAXX(

                    FILTER(

                        LatestDates,

                        Tasks[Deliverable_ID] = EARLIER(Tasks[Deliverable_ID]) &&

                        Tasks[Task] = EARLIER(Tasks[Task])

                    ),

                    [LatestDate]

                )

        )

    )

I have attached the report and Data file, If you could point out the issue in the formula that would be great. Thank you
 Sample Data 
Report 

[danextian]

your google drive link requires a permission

[Jack00711]

@danextian Apologies about that, here are the updated links : 
 Sample Data 
Report 

[Anonymous]

Hi,

Thanks for the solutions grazitti_sapna , danextian  ,Sergii24 , and Bibiano_Geraldo  offered, and i want to offer some more informaiton for user to refer to.

hello @Jack00711 , if you want to display the date before the date you selected, you need to remove the relationship between the tables. then create a measure.

Measure = CALCULATE(SUM(Tasks[Value]),FILTER(Tasks,Tasks[Date]<=MAX('Calendar'[Date])))

Then create a table visual, put the measure and the data column of task table to the visual.

Put the date of calendar column to the slicer.

Output

Best Regards!

Yolo Zhu

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