created and deleted items in one matrix table

[cousinitt13]

Hello,

I'm struggling with some, in my opinion, very easy problem.

There is a table with items including creation and delete date

Item	creation date	deletion date
1	April	April
2	May	April
3	April	May
..	...

I'd like to have two measures, where I can select for example "April",
and I get measure "all_created" and "all_closed" for this month .

one my formular see below.   the problem here is, it is filtered by creation_date also 

TASK (SRQ) - parent Object closed date =

CALCULATE(
DISTINCTCOUNT('ServiceReq'[Number]),
FILTER(ServiceReq,
('ServiceReq'[Closed Date]) >= MIN('Closed Date'[Date]) &&
('ServiceReq'[Closed Date]) <= MAX('Closed Date'[Date])
))

can anybody help in this case?

[v-kkf-msft]

Hi @cousinitt13 ,

I am not sure if I understood your question correctly. If I use the ALL function, it does not remove the filter on table 'Closed Date'. Could you please share your calculation results?

parent Object closed date = 
CALCULATE (
    DISTINCTCOUNT ( 'ServiceReq'[Number] ),
    FILTER (
        ALL ( ServiceReq ),
        ( 'ServiceReq'[Closed Date] ) >= MIN ( 'Closed Date'[Date] )
            && ( 'ServiceReq'[Closed Date] ) <= MAX ( 'Closed Date'[Date] )
    )
)

parent Object created date = 
CALCULATE (
    DISTINCTCOUNT ( 'ServiceReq'[Number] ),
    FILTER (
        ALL ( ServiceReq ),
        'ServiceReq'[Created Date] >= MIN ( 'Created Date'[Date] )
            && 'ServiceReq'[Created Date] <= MAX ( 'Created Date'[Date] )
    )
)

Best Regards,
Winniz

[tamerj1]

Hi @cousinitt13 
You need only one disconnected date table

TASK (SRQ) - parent Object closed date =
CALCULATE (
    DISTINCTCOUNT ( 'ServiceReq'[Number] ),
    'ServiceReq'[Closed Date] >= MIN ( 'Date'[Date] ),
    'ServiceReq'[Closed Date] <= MAX ( 'Date'[Date] )
)

TASK (SRQ) - parent Object open date =
CALCULATE (
    DISTINCTCOUNT ( 'ServiceReq'[Number] ),
    'ServiceReq'[Open Date] >= MIN ( 'Date'[Date] ),
    'ServiceReq'[Open Date] <= MAX ( 'Date'[Date] )
)

[cousinitt13]

Not having the opportunity to create relationships and new tables

[tamerj1]

@cousinitt13 

Alright, I guess in this case also REMOVEFILTERS function is not available. Then you can use one of the date tables in the slicer (I guess better than using two slicers) and use this code

TASK (SRQ) - parent Object open date =
CALCULATE (
    DISTINCTCOUNT ( 'ServiceReq'[Number] ),
    ALL ( 'Created Date' ),
    ALL ( 'Closed Date' ),
    'ServiceReq'[Open Date] >= MIN ( 'Closed Date'[Date] ),
    'ServiceReq'[Open Date] <= MAX ( 'Closed Date'[Date] )
)

TASK (SRQ) - parent Object Created date =
CALCULATE (
    DISTINCTCOUNT ( 'ServiceReq'[Number] ),
    ALL ( 'Created Date' ),
    ALL ( 'Closed Date' ),
    'ServiceReq'[Created Date] >= MIN ( 'Closed Date'[Date] ),
    'ServiceReq'[Created Date] <= MAX ( 'Closed Date'[Date] )
)

[cousinitt13]

Hello,
Thanks for the quick answer. I'm understanding this way you sent me, my problem is, I didn't have the possibility to create this datetable cause of restrictions of this dataset.
is there any other way??

If I use the ALL() function, the "closed date" filter seems to be ignored

TASK (SRQ) - parent Object closed date =

CALCULATE(

DISTINCTCOUNT('Task for ServiceReq'[Task Number]),

FILTER(ALL(ServiceReq),

(ServiceReq[Converted To Incident] = FALSE) &&

('ServiceReq'[Closed Date]) >= MIN('Closed Date'[Date]) &&

('ServiceReq'[Closed Date]) <= MAX('Closed Date'[Date])

)

)

BR

[cousinitt13]

this is the relation model 

[amitchandak]

@cousinitt13 , If you need close and created on the same period you can work with one date table with Active inactive join and userelationship (to be used in measure)

refer example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...