DAX : count of total tickets which are either created or resolved with in the selected date range

[Anonymous]

Dear Community,

(Attaching the sample data and the PBIX )

What i need is all the tickets which are either created between selected dates or resolved between selected dates.  Date would be a role playing dimension.

3 Measures

Total Tickets created or resolved - Count of tickets which are either created or resolved between the selected dates

i.e if a ticket xxx is created before jan 1st but resolved after jan 1st that should be part of Total Tickets created or resolved

Total Tickets created - Count of tickets which are either created in the selected time frame or created and resolved in the selected time frame

Total Tickets created - Count of tickets which are either created and resolved in the selected time frame or only resolved in the selected time frame...

Thanks,

Nishanth.

[Anonymous]

to be more specific - what i need is to convert below query to DAX

select [id],Created,ResolutionDate from data where
project = 'CSR'
and ( Created between '2021-03-07' and '2021-03-13' or ResolutionDate between '2021-03-07' and '2021-03-13' )
order by Created

[v-lionel-msft]

Hi @Anonymous ,

select [id],Created,ResolutionDate from data where
project = 'CSR'
and ( Created between '2021-03-07' and '2021-03-13' or ResolutionDate between '2021-03-07' and '2021-03-13' )
order by Created

Try to create such a calculated table.

Table = 
SUMMARIZE(
    FILTER(
        data,
        ( ([CreatedDate] >= DATE(2021,3,7) && [CreatedDate] <= DATE(2021,3,13)) || ([ResolvedDate] >= DATE(2021,3,7) && [ResolvedDate] <= DATE(2021,3,13)) ) && [project] = "CSR"
    ),
    [Ticket ID], [CreatedDate], [ResolvedDate]
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

yes i did this, what in need is how to make the dates given there dynamic...

[v-lionel-msft]

Hi @Anonymous ,

It is not possible to create a dynamic calculation table, so you still need to consider creating measures and adding them to a table visual.

The reason why I removed the table relationship when I first responded to you is because I think the relationship between your two tables is meaningless and will cause statistical results to be wrong. Or is my understanding wrong?

Are the results returned by the following three measures correct?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-lionel,

Thanks for your reply. I see you have removed the relationship between DimDate and Data tables, but i need that relationship to be available and achieve the same.

Thanks,

Nishanth.

[selimovd]

Hey @Anonymous ,

where exactly are you struggling?

Best regards

Denis

[Anonymous]

Hi Denis,

I get this data from Jira, where i can write a query which pulls all the records which are either created or resolved with in dates.

But in Power BI, i am not able to exactly get that functionality.

I need an active relationship between DimDate and Ticket Data on Created Date and an inactive relationship between DimDate and resolved date.

When i select a date range, i can get the created tickets in the date range, but what i also need is there can be tickets created before that date range which are resolved in those days.

Thanks,

Nishanth.