@Aishwaryazanpur
To create a data completeness check in Power BI that flags columns with a value of 0 beyond the 5th working day of each month, you can follow these steps. This involves creating a calculated column for the deadline date and then using a measure or additional calculated column to evaluate the completeness based on your criteria.
Step 1: Calculate the 5th Working Day of Each Month
You'll first need to create a calculated column to determine the 5th working day of each month. This requires some DAX logic to account for weekends and potentially holidays (assuming standard weekends and no holiday adjustments):
DATE Deadline =
VAR BaseDate = STARTOFMONTH('Table'[DATE])
VAR AddDays = 5 + (5 - WEEKDAY(BaseDate, 2) + 1) / 7 * 2 + (WEEKDAY(BaseDate, 2) = 1) * 1
RETURN
WORKDAY(BaseDate, AddDays)
This formula calculates the 5th working day from the start of each month. It adjusts for weekends assuming a Monday (2) start of the week. `WORKDAY` function is used, which needs to be adjusted if you are considering public holidays (additional parameter can be passed to `WORKDAY` for holidays).
Step 2: Identify Columns with Zero Values after the Deadline
To find columns where values are 0 beyond the 5th working day, you'll need to create a measure (or several measures) depending on how many columns you're checking. For example, if you want to check a specific column named `Column1`, you could use:
Column1 Check =
IF(MAX('Table'[DATE]) > MAX('Table'[DATE Deadline]) && SUM('Table'[Column1]) = 0, "Check Failed", "Check Passed")
This DAX measure checks if the maximum date in your data exceeds the deadline and if the sum of `Column1` is 0, indicating incompleteness.
Step 3: Applying This to Multiple Columns
If you need to check multiple columns, repeat the measure creation for each column you need to check. Alternatively, if the logic applies uniformly across multiple columns, consider creating a more dynamic measure using `SUMMARIZE` and `CALCULATE` to loop through column names dynamically, though this approach can get quite complex and might impact performance.
Step 4: Create a Visual for Reporting
To use these measures in your reports:
- Add a table visual or matrix to your report.
- Drag the date fields and the new measures (e.g., `Column1 Check`) into the visual.
- This setup will display the check results across your dates and columns.
Additional Considerations
- If holidays affect what counts as a working day, you'll need to incorporate a holiday calendar in your DAX formulas.
- Be mindful of the performance implications when adding complex calculated columns and measures, especially in large datasets.
By following these steps, you can create a robust data completeness check report in Power BI tailored to your specific needs regarding working days and completeness criteria.
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
