topic Sorting items by open/closed with year slicer in DAX Commands and Tips

Sorting items by open/closed with year slicer

Anonymous — Mon, 04 Dec 2023 18:39:21 GMT

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!

Re: Sorting items by open/closed with year slicer

ray_codex — Mon, 04 Dec 2023 20:18:40 GMT

Can you show a data sample and the columns that mark an open and/or closed audit?

Re: Sorting items by open/closed with year slicer

Anonymous — Mon, 04 Dec 2023 21:11:42 GMT

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

Re: Sorting items by open/closed with year slicer

ray_codex — Mon, 04 Dec 2023 21:45:21 GMT

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.