Finding delta between two dates for hierarchical data

[sv26]

My question is more conceptual one and wondering if this is even possible in PowerBI?

I have a hierarchical data model to represent my data collection: a report having many to many relationship with files which has one to many relationship with blocks.

For this, I have 4 tables:

- Report table (with date column) which mainly has a date column, id column

- ReportFiles many to many table 

- Files table which mainly has path column along with other file data

- Blocks table which mainly has hash column along with other block data

I need the user to specify any two dates using slicers to calculate the delta between two reports. The delta means finding what exactly changed between the two dates for all hierarchical data. For files, there is a column for path so understanding which paths were added/removed is important. For blocks, there is a hash value so understanding which hashvalues changed is important. 

For results, I need to present the collection of list of data that changed along with the numerical measures. For example, the visuals should display the file paths and block hash values added and removed along with the counts. The lists and counts should dynamically update in visual if user selects different data ranges.

Is this possible?

[Sahir_Maharaj]

Hello @sv26,

Yes, it is possible to achieve this in Power BI using slicers, measures, and visuals. Here's a high-level approach to accomplish your goal:

1. Create two slicers for date selection:

   • Add two slicers to your report canvas and connect both to the date column in the Report table.
   • Change the slicer type to "Date Range" for both slicers.
   • Name one slicer "Start Date" and the other "End Date."

2. Create measures to calculate the differences between the two dates:

   • Create a measure to identify the two reports by the selected dates. Use the SELECTEDVALUE function to get the report ID based on the selected date range.
   • Create measures to find the file paths and block hash values added and removed between the two reports. Use functions like CALCULATE, FILTER, and EXCEPT to get the differences between the two sets.
   • Create measures to count the number of file paths and block hash values added and removed.

3. Display the results in visuals:

   • Use tables or matrix visuals to display the lists of file paths and block hash values added and removed.
   • Use card visuals to display the counts of file paths and block hash values added and removed.

Let me know if you might require further assistance.

[sv26]

Hi Sahir, 

Can you expand on how I can display the list of file paths using measure? Doesn't a measure only return a single value that is often numerical? Can you return column in a measure?

Thanks,

Saket