Forum Discussion
Sorting items by open/closed with year slicer
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!
3 Replies
- ray_codex
Resolver II
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
- ray_codex
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.
