DATE GRANUALITY

[aimanhafez130]

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 month and etc.

So How can i make lets saya current date is 15/07, then i choose this week(it will show you 14/07-20/07). So when i drillthrough to the montly basis data, it will still show you 01/07 since the data is for july period.

Thank you.

[v-karpurapud]

Hi @aimanhafez130 

We have not yet received a response from you regarding your query. If the previous response was helpful, please let us know. If not, feel free to provide more details so we can assist you further.

Thank you.

[v-karpurapud]

Hi @aimanhafez130 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @aimanhafez130 

We have not yet heard back from you about whether our response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

[v-karpurapud]

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.

[rohit1991 · Accepted solution]

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.

[FBergamaschi]

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