Preparing a operational dashboard

Hi ketay - If you want to display the total number of unique devices without summing up all the weekly values,

you can use DISTINCTCOUNT function to get the same. 

DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn

2.Ensure you have a date table marked as a "Date Table" in Power BI. This table should include columns for Year, Month, Week, and any other needed date hierarchies.Create Measures for Month and Week Analysis

3. If your commentary data is only on a monthly level, you can join it using a relationship on the month field

using calculated column:

Month = FORMAT('Date'[Date], "YYYY-MM")

Use a LOOKUPVALUE or a relationship-based approach to bring the commentary into your visuals.

Hope this works. please check.

Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn

Hi ketay ,

Total Devices Without Double Counting:
Rather than adding a "total" column that may double-count the same device because data is logged weekly, make a measure out of a distinct count on your device identifier (e.g., Asset Name or Device ID). For instance:
Total Devices = DISTINCTCOUNT('Data'[Asset Name])

This measure will only count each device once, no matter how many times it shows up in the data.

Interaction Between Month and Week
In order to facilitate smooth interactivity between week and month views, develop an all-encompassing Date table with year, month, week, etc. fields. Have a relationship with your fact table based on the 'reported date' field from this Date table. And, in your charts, employ the hierarchy of Date table (i.e., Year > Month > Week) or individual slicers for Month and Week. This will enable users to drill down from a monthly overview into weekly information or filter the data at varying levels of granularity.

Joining with Monthly Commentary Data:
As your commentary data exists at a monthly level, employ your Date table as an interface between the commentary table and fact table. Ensure that the Date table contains a month column to match the commentary data. Create a relationship from the commentary table to the Date table on the month field next. This allows commentary to filter or display alongside your weekly data but remains tied to the correct month.

Following these steps should assist you in properly counting devices, generating interactive month-to-week views, and joining commentary data with your primary dataset smoothly.
 
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

Thankyou, rajendraongole1,grazitti_sapna, for your response.

Hi ketay,

We appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Based on my understanding, please follow the steps outlined below, which may help resolve the issue:

1. Use the DAX formula provided to create a comprehensive Date Table with Year, Month, and Week columns. Ensure that this table is marked as a Date Table.
   
   DateTable =
   ADDCOLUMNS(
   CALENDAR(DATE(2024,1,1), DATE(2025,12,31)),
   "Year", YEAR([Date]),
   "Month", FORMAT([Date], "MMM YYYY"),
   "MonthNumber", FORMAT([Date], "YYYYMM"),
   "Week", WEEKNUM([Date],2),
   "Week Ending", [Date] + (7 - WEEKDAY([Date],2))
   )

2. Establish relationships by connecting the Reported Date column from your fact table to the Date column in the Date Table.

3. Instead of using DISTINCTCOUNT, please create the measures indicated to correctly aggregate your values.
   Total Devices = SUM('YourDataTable'[Total])
   Active Devices = SUM('YourDataTable'[Active])
   Inactive Devices = SUM('YourDataTable'[Inactive])
   Performance (%) = DIVIDE([Active Devices], [Total Devices], 0)
   Total Devices (Monthly) =
   CALCULATE([Total Devices], REMOVEFILTERS('DateTable'[Week]))

4. Use the Date Hierarchy (Year > Month > Week) in your visuals and enable Drill Mode to smoothly navigate from months to weeks.

5. Add a Month column in your Commentary Table and relate it to the Month column in the Date Table to align comments with your monthly data.
   CommentaryMonth = FORMAT('CommentaryTable'[CommentDate], "MMM YYYY")
   
   

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.