Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 3
ray_codex
Resolver II
Resolver II

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.

 

ray_codex
Resolver II
Resolver II

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

Anonymous
Not applicable

@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 NameItem TitleAudit Release DateSub-Item TitleSub-item Implementation DateSub-item Implementation or Due DateItem Last Sub-item DateAudit Last sub-item Date
Audit 1Item 1.110/1/2020Sub-item 1.1.13/20/20213/20/20213/20/20212/10/2022
Audit 1Item 1.110/1/2020Sub-Item 1.1.212/31/202012/31/20203/20/20212/10/2022
Audit 1Item 1.210/1/2020Sub-item 1.2.11/3/20221/3/20221/3/20222/10/2022
Audit 1Item 1.210/1/2020Sub-item 1.2.21/3/20221/3/20221/3/20222/10/2022
Audit 1Item 1.310/1/2020Sub-item 1.3.12/10/20222/10/20222/10/20222/10/2022
Audit 2Item 2.12/1/2021Sub-item 2.1.110/8/202110/8/202110/8/202111/2/2023
Audit 2Item 2.22/1/2021Sub-item 2.2.110/9/202110/9/202110/9/202111/2/2023
Audit 2Item 2.32/1/2021Sub-item 2.3.112/24/202212/24/202212/24/202211/2/2023
Audit 2Item 2.42/1/2021Sub-item 2.4.112/31/202212/31/202212/31/202211/2/2023
Audit 2Item 2.42/1/2021Sub-item 2.4.25/10/20215/10/202112/31/202211/2/2023
Audit 2Item 2.42/1/2021Sub-item 2.4.35/10/20215/10/202112/31/202211/2/2023
Audit 2Item 2.52/1/2021Sub-item 2.5.111/2/202311/2/202311/2/202311/2/2023
Audit 3Item 3.14/1/2022Sub-item 3.1.112/31/202212/31/20228/10/20235/1/2024
Audit 3Item 3.14/1/2022Sub-item 3.1.21/1/20231/1/20238/10/20235/1/2024
Audit 3Item 3.14/1/2022Sub-item 3.1.38/10/20238/10/20238/10/20235/1/2024
Audit 3Item 3.24/1/2022Sub-item 3.2.1NA4/1/20244/1/20245/1/2024
Audit 3Item 3.24/1/2022Sub-item 3.2.212/4/202312/4/20234/1/20245/1/2024
Audit 3Item 3.34/1/2022Sub-item 3.3.1NA4/1/20245/1/20245/1/2024
Audit 3Item 3.34/1/2022Sub-item 3.3.2NA5/1/20245/1/20245/1/2024
Audit 3Item 3.34/1/2022Sub-item 3.3.311/5/202311/5/20235/1/20245/1/2024
Audit 4Item 4.17/1/2023Sub-item 4.1.1NA12/31/20232/1/20242/1/2024
Audit 4Item 4.17/1/2023Sub-item 4.1.2NA1/12/20242/1/20242/1/2024
Audit 4Item 4.17/1/2023Sub-item 4.1.3NA2/1/20242/1/20242/1/2024

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.