Return all column names from table whose values are zero after a deadline date

Anonymous 

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 👍

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Thank you AnalyticsWizard  and Greg_Deckler  for your prompt reply.

Hi Anonymous ,
As Greg_Deckler  said, could you provide some example data such as detailed data (without sensitive information) and your desired output. This will allow us to better understand and solve the problem you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.