Forum Discussion

aimanhafez130's avatar
aimanhafez130
Regular Visitor
1 year ago
Solved

DATE GRANUALITY

Hye Everyone, I have one two data that based on daily and montly basis. For monthly basis, It willl show you like 01/06,01/07 and etc. I have build a date type like this week, last week,  this ...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

     

rohit1991's avatar
rohit1991
Super User
1 year ago

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.