Forum Discussion
DATE GRANUALITY
Hi aimanhafez130 ,
You want to use different date granularities (daily vs monthly) and keep your filters consistent when drilling through.
When you drill through from daily/weekly data to monthly data (e.g., selecting “This Week”), Power BI will filter the monthly data by the date(s) from the drillthrough. Since your monthly data is summarized by the first of each month (e.g., 01/07 for July), you need to “translate” the daily/weekly selection into the correct month.
Heres how:
-
Create a Month Key Column in both your fact tables (daily and monthly) using DAX:
MonthKey = FORMAT([Date], "YYYYMM") -
When drilling through, pass the MonthKey instead of the exact date.
-
In your drillthrough filter setup, use MonthKey as the field—so selecting 14/07-20/07 will drill to 01/07, because both share the same MonthKey ("202407").
This way, your monthly view always aligns with your selected period, showing the whole month’s data even if you started from a weekly/daily context.
-
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here