Calculate the distinct count based on Status and Closed date

[a4]

I have table (quotes) which has quotestatus column,createdon date column, closedon date column,quotereference column and a club year coulmn.

Club year logic is:   Club Year = IF(DATE(quotes[createdon].[Year],2,20)>quotes[createdon].[Date],quotes[createdon].[Year]-1,quotes[createdon].[Year])

Club year starts from 20th February of a particular year to 20th of Feb next year.Club year is created using created on column.

Now let's move to the main requirement:

I want to count the distinct quotereference number based on quotestatus but created date and closed on should fall under same club year. 

If createdon date and closedon date falls in two different club year then the distinct count of the quote will be in the club year which contains the closed date.

Please refer the screenshot for better understanding:

As you can see in the above screenshot that createdon date and closedon date date falls in two different club years, so these quotes will be counted in 2019 club year because closed on date falls under 2019 club year.

Please find below the screenshot of entire table.

[v-deddai1-msft · Accepted solution]

Hi,

Would you please show us your expected output more clearly?

measure =

CALCULATE (

    COUNT ( quotes[quotereference] ),

    FILTER (

        SUMMARIZE (

            quotes,

            quotes[quotereference],

            quotes[closedon],

            quotes[Club Year],

            quotes[closedon],

            quotes[quotestatus],

            "cYear", IF (

                DATE ( quotes[createdon].[Year], 2, 20 ) > quotes[closedon],

                quotes[closedon].[Year] - 1,

                quotes[closedon].[Year]

            )

        ),

        [cYear] = quotes[Club Year]

            && quotes[quotestatus] = "Declined"

    )

)

You can create measure like the measure above. It will be nice, if you can inform us more detailed information( your  data(by OneDrive for Business)) if possible for test? Then we will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[amitchandak]

@a4 , refer if this article can help

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