Forum Discussion
Custom table is not working when filtering on date
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 🙂
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.
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.
Sample Data
Report
your google drive link requires a permission
danextian Apologies about that, here are the updated links :
Sample Data
Report
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.
