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.

     

v-karpurapud's avatar
v-karpurapud
Icon for Community Support rankCommunity Support
1 year ago

Hi aimanhafez130 


Thank you for contacting the Microsoft Fabric Community Forum.

 

To achieve accurate drillthrough functionality across varying date granularities, as rohit1991 suggested, it is effective to use a standardized MonthKey in both your daily and monthly tables. You can accomplish this by adding a column with the DAX expression MonthKey = FORMAT([Date], "YYYYMM") in each dataset and configuring drillthrough to use this key instead of the raw Date. This approach enables Power BI to correctly map selected dates, such as those from a weekly range, to the appropriate month in your summarized data.This solution is particularly beneficial when your datasets do not require a comprehensive date dimension or advanced time intelligence, as it provides consistent filtering and enhances the accuracy and usability of your drillthrough reports.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.