First occurence with multiple conditions

[BenMagn]

Hi, I am new to DAX and would like to create a measure that produce a column called [Rookie year] to count the first occurence of an individual that purchased an item based on other variables. 

those are the other conditions to be considered in order to be counted as first occurence : 

- His client number wich is unique to him and stay's the same year after year is called [N certificat]

- In the column [Statut certificat] It as to be "Permanent"

- The date of [Date certificat] as to be within 5 years of the date of purchase wich is the column [Date emission permis] in order to count

So each [N certificat] as to appear only once in the column rookie year.

see picture below for a more visual look of an example table. the datas are not the real datas so the occurence dont match.

Ive fiddled with some of the formulas found in the forum but couldnt find one that I am able to translate to my needs. 

Thanks very much !

[BenMagn]

Hope this is easier to understand ! thanks!

[Anonymous · Accepted solution]

Hi @BenMagn ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Rookie year = 
VAR _selncer =
    SELECTEDVALUE ( 'Table'[N certificat] )
VAR _mindep =
    CALCULATE (
        MIN ( 'Table'[Date emission permis] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[N certificat] = _selncer )
    )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[N certificat] ),
        FILTER (
            'Table',
            'Table'[N certificat] = _selncer
                && 'Table'[Date emission permis] = _mindep
                && 'Table'[Statut certificat] = "Permanent"
                && 'Table'[Date emission permis] - 'Table'[Date certificat] <= 5
        )
    )
RETURN
    IF ( _selncer IN _tab, 1, 0 )

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[FreemanZ]

hi @BenMagn 

Unable to understand your expectation, could you provide also the expected result?