count based on the first filter

[dgdgdg122db]

Hi 

I want to count the no. of solved tickets that filter two dates: 1) created  and 2) resolved

If I do it in excel:

first,

No. of Created tickets: select everything that was created from the beginning (2018) until 2019-02

= 144

No. of resolve tickets: keep the filter that set on Created and then filter the resolved everything that is from 2019-02 until now

=53

can I do this in Power Bi and how ?

my excel sheet is https://drive.google.com/file/d/1WP6TNJghWzUJU9ZGg_S12FzbgfWPhEfJ/view?usp=sharing

[v-lid-msft]

Hi @dgdgdg122db ,

We can try to create the following calculated column in the Calendar table:

Resolved tickets =
CALCULATE (
    DISTINCTCOUNT ( 'Resolved'[Issue id] ),
    FILTER ( 'Resolved', [Resolved] >= [Date] )
)

Or create a measure used with the date column of Calendar table:

Resolved tickets =
CALCULATE (
    DISTINCTCOUNT ( 'Resolved'[Issue id] ),
    FILTER (
        ALLSELECTED ( 'Resolved' ),
        'Resolved'[Resolved] >= MAX ( 'Calendar'[Date] )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[dgdgdg122db]

I think my explanation was not clear.

But for example, I am using excel and I want to count the followings:

1. No. of Created tickets: select everything that was created from the beginning (2018) until 2019-02

2. No. of resolve tickets: keep the filter that set on Created and then filter the resolved everything that are from 2019-02 until now.

Basically, I need to first select the date on created, and then keep this selection and then select the resolved. In this case, the resolve date is greater or equal to the created date.

In excel: first, select everything until  Feb. the count of created is 144

And then, keep the filter that was on created, and now select resolved everything is greater or equal to the created date. The count from Feb onwards is 53

my excel file is here https://drive.google.com/file/d/1WP6TNJghWzUJU9ZGg_S12FzbgfWPhEfJ/view?usp=sharing

[Anonymous]

In your 'solved' table, I saw 'created' and 'status' fields already.  Why do you need to join with other tables?  You should be able to to get your answer within the 'solved' table.  If not, please post your 'solved' table.

Best,

Eric Ji | Senior Business Intelligence Consultant
www.designmind.com

[amitchandak]

Not sure why you have another table for resolved. You can join both dates with date table. One will active join another one will be inactive join.

Refer how to use userelation to change date join

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

Once they in same table you can easily add a filter in the calculation resolve date and created date

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601