Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all -
I have a dataset of audits, which includes informaton about when an audit was released, and when it's last sub-component was implemented (or when it will be in the future). Each audit is listed many times as there is a row for each of its sub-components. I need formulas for both the number of open and closed audits that work for real-time/present, or based on a year slicer that filters by audit end date. In other words, when the present/future years are selected, it shows the number open right now and the number closed this year, but if I select a past year (or set of years), say 2022, it will show how many audits were still open at the end of 2022 and how many were closed during 2022.
I had originally thought a formula like this would work for closed, but it's returning blank when I know there are audits that should be counted. I'm also relatively new to powerbi and dax, so any help is appreciated, I've been stuck on this for a while.
# of closed audits
CALCULATE(
DISTINCTCOUNT('data'[audit title]), FILTER(ALL('data'), DATEDIFF('data'[Release Date], DATE('data'[max_selected_year, 12, 31), DAY ) > 0), FILTER(ALL('data'), DATEDIFF('data'[Final Implementation Date], DATE('max_selected_year, 12, 31), DAY)> 0))
Thank you!
Ok, I think I got it. This assumes you have a date dimension for filtering. For audits that are closed:
audits closed =
VAR min_date = MIN('Date'[Date])
VAR max_date = MAX('Date'[Date])
return
CALCULATE(
DISTINCTCOUNT(audits[Audit Name]),FILTER(audits,
audits[Audit Last sub-item Date].[Date] <= max_date && audits[Audit Last sub-item Date] >= min_date )
)
And for audits that are still open:
audits open =
VAR max_date = MAX('Date'[Date])
return
CALCULATE(
DISTINCTCOUNT(audits[Audit Name]),FILTER(audits,
audits[Audit Last sub-item Date].[Date] > max_date)
)
This also states that there is no active relationship between the date table and your audits table.
Hope it works for you.
Can you show a data sample and the columns that mark an open and/or closed audit?
@ray_codex yes of course. the data itself is confidential but I've included a table of 4 made-up audits with items and sub-items. You can see every audit has items, which itself has subitems. The sub-items have timelines, and you can see some sub-items have been implemented already, and some have not (NAs). The 6th column replaces the NA data with the future due date that the sub-item will be implemented (aka closed). Then the final 2 columns show the date the the associated finding or audit is/will be closed (i.e. the date of its last sub-item).
Audit Name | Item Title | Audit Release Date | Sub-Item Title | Sub-item Implementation Date | Sub-item Implementation or Due Date | Item Last Sub-item Date | Audit Last sub-item Date |
Audit 1 | Item 1.1 | 10/1/2020 | Sub-item 1.1.1 | 3/20/2021 | 3/20/2021 | 3/20/2021 | 2/10/2022 |
Audit 1 | Item 1.1 | 10/1/2020 | Sub-Item 1.1.2 | 12/31/2020 | 12/31/2020 | 3/20/2021 | 2/10/2022 |
Audit 1 | Item 1.2 | 10/1/2020 | Sub-item 1.2.1 | 1/3/2022 | 1/3/2022 | 1/3/2022 | 2/10/2022 |
Audit 1 | Item 1.2 | 10/1/2020 | Sub-item 1.2.2 | 1/3/2022 | 1/3/2022 | 1/3/2022 | 2/10/2022 |
Audit 1 | Item 1.3 | 10/1/2020 | Sub-item 1.3.1 | 2/10/2022 | 2/10/2022 | 2/10/2022 | 2/10/2022 |
Audit 2 | Item 2.1 | 2/1/2021 | Sub-item 2.1.1 | 10/8/2021 | 10/8/2021 | 10/8/2021 | 11/2/2023 |
Audit 2 | Item 2.2 | 2/1/2021 | Sub-item 2.2.1 | 10/9/2021 | 10/9/2021 | 10/9/2021 | 11/2/2023 |
Audit 2 | Item 2.3 | 2/1/2021 | Sub-item 2.3.1 | 12/24/2022 | 12/24/2022 | 12/24/2022 | 11/2/2023 |
Audit 2 | Item 2.4 | 2/1/2021 | Sub-item 2.4.1 | 12/31/2022 | 12/31/2022 | 12/31/2022 | 11/2/2023 |
Audit 2 | Item 2.4 | 2/1/2021 | Sub-item 2.4.2 | 5/10/2021 | 5/10/2021 | 12/31/2022 | 11/2/2023 |
Audit 2 | Item 2.4 | 2/1/2021 | Sub-item 2.4.3 | 5/10/2021 | 5/10/2021 | 12/31/2022 | 11/2/2023 |
Audit 2 | Item 2.5 | 2/1/2021 | Sub-item 2.5.1 | 11/2/2023 | 11/2/2023 | 11/2/2023 | 11/2/2023 |
Audit 3 | Item 3.1 | 4/1/2022 | Sub-item 3.1.1 | 12/31/2022 | 12/31/2022 | 8/10/2023 | 5/1/2024 |
Audit 3 | Item 3.1 | 4/1/2022 | Sub-item 3.1.2 | 1/1/2023 | 1/1/2023 | 8/10/2023 | 5/1/2024 |
Audit 3 | Item 3.1 | 4/1/2022 | Sub-item 3.1.3 | 8/10/2023 | 8/10/2023 | 8/10/2023 | 5/1/2024 |
Audit 3 | Item 3.2 | 4/1/2022 | Sub-item 3.2.1 | NA | 4/1/2024 | 4/1/2024 | 5/1/2024 |
Audit 3 | Item 3.2 | 4/1/2022 | Sub-item 3.2.2 | 12/4/2023 | 12/4/2023 | 4/1/2024 | 5/1/2024 |
Audit 3 | Item 3.3 | 4/1/2022 | Sub-item 3.3.1 | NA | 4/1/2024 | 5/1/2024 | 5/1/2024 |
Audit 3 | Item 3.3 | 4/1/2022 | Sub-item 3.3.2 | NA | 5/1/2024 | 5/1/2024 | 5/1/2024 |
Audit 3 | Item 3.3 | 4/1/2022 | Sub-item 3.3.3 | 11/5/2023 | 11/5/2023 | 5/1/2024 | 5/1/2024 |
Audit 4 | Item 4.1 | 7/1/2023 | Sub-item 4.1.1 | NA | 12/31/2023 | 2/1/2024 | 2/1/2024 |
Audit 4 | Item 4.1 | 7/1/2023 | Sub-item 4.1.2 | NA | 1/12/2024 | 2/1/2024 | 2/1/2024 |
Audit 4 | Item 4.1 | 7/1/2023 | Sub-item 4.1.3 | NA | 2/1/2024 | 2/1/2024 | 2/1/2024 |
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |