Default Date Filters using Calculation Groups - How to easily build in Power BI
Vložit
- čas přidán 30. 07. 2024
- Setting default date filters using Calculation Groups and easily build it in Power BI. With built in features in Power BI, it not easy If you need to dynamically filter to a certain date range such as today, current month, the past 7 days, and so on . I am sharing a technique here on how to achieve it using DAX and Calculation groups in Power BI.
The technique shown here can mostly be adapted to set any type of date filters that you may need to have in your Power BI report. On opening the report, users could be presented with data having default date filters applied. Most importantly, you can also allow them to apply their own date filters.
I will take you through the complete steps from scratch and show you how to build the dynamic date filters using the Tabular Editor.
🟠 You may download the completed PBIX file here:
excelfort.com/setting-default...
🟠 Download link for Tabular Editor 2
github.com/TabularEditor/Tabu...
🟠 LET'S CONNECT!
-- / excelfort
-- / excelfort
-- / fowmy
🟠 Subscribe to this CZcams Channel: czcams.com/users/excelfort?s...
🟠 Subscribe to our blog for more content like this: excelfort.com/
#powerbi #datavisualization #dax #dataanalytics
Excellent explanation
Very helpful! Thanks!
You are most welcome 🙏
Excellent explanation👍
Glad you think so!
How to make Date slicer & Visual interaction, Default dates and dates slicer date should match
I have tried this solution and it works well but what if the user wants to see the data for the complete date range then it shows the default data
Love this! Do you think it'll work well with multiple fact tables within a model? Currently playing around with this concept in a monthly newsletter for stakeholders with my team.
Thanks!
Yes, you can duplicate the calculation item and replace the last sales date with the date in your 2nd fact table and apply as I did with 1st one. Hope you have a relationship your dates table and the two fact table
@@ExcelFort can we connect for the same issue..
Thanks but why do you need to wrap a calculate with allselected around the isfiltered option instead of checking for isfiltered only?
That is a very good question.
I have used the Dates[Date] column in the date slicer and in the visuals. When the Calculation Group is applied, it has to determine if the date slicer is filtered, at the same time, I need to ensure that I Ignore ISFILTERED is not checking on the Dates[Date] column added inside the visuals. I modified the filter context by removing filters within the visual (Query) using CALCULATE and ALLSELECTED then evaluated the ISFILTERED results.
Hope it made it clear?
@@ExcelFort Thanks
Is there any possibility to create column in Power BI It self Using DAX. Without external tools
I am not sure the context to your question but to create a column on a table using DAX, you can add a new column from the ribbon. Let me know if your question is different
A common problem. But have you looked for custom visual to rectify this issue
I think even custom visuals needs to work with the existing filter context. I haven’t tried any.
When I use that default date option some kpi are not getting filters can you please elaborate or can you please make more videos on same scenario.
Yes, I will be doing videos using this technique to solve similar challenges, stay tuned.
You can create a sample Power BI file highlighting your issues and email me at info@excelfort.com
@@ExcelFort okay Thanks..
@@bhushanrakshe6110 you can use all on that kpi where it's effecting, i think it will work
Can we have the custom date slicer default to 7 days
Yes, there a possibility to achieve it but with a different technique, the downside is, users will not be able modify the date range. Also, there are multiple ways all involving complicated approaches.
@@ExcelFort : I have the same question. Is it possible to set the slicer automatically filter on the 7 last days at the initialization of report and let the possibility to the user to scroll on previous month / year ?