Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 l...
ray_codex's avatar
ray_codex
Icon for Resolver II rankResolver II
2 years ago

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.