Need Help with Dax (Display Occurrence Number In Calculated Column)

[Bpark1994]

Hello,

Say i have a table with 2 columns and dates out of order like so

Date	Color
1/1/2017	blue
2/5/2017	blue
1/24/2017	blue
6/25/2017	red
9/1/2017	red
4/25/2017	red
3/26/2017	green
2/22/2017	green
9/24/2017	green
5/26/2017	blue

I want to be able to create a 3rd calculated column that will give me this...

Date	Color	Occurrence Number
1/1/2017	blue	1
2/5/2017	blue	3
1/24/2017	blue	2
6/25/2017	red	2
9/1/2017	red	3
4/25/2017	red	1
3/26/2017	green	2
2/22/2017	green	1
9/24/2017	green	3
5/26/2017	blue	4

Thanks for the help!

[TomMartens · Accepted solution]

Hey,

the DAX statement to create the calculated column looks like this:

Rank by Date in group color = 
RANKX (
    FILTER (
        'Table2'
        ,'Table2'[Color] = EARLIER ( Table2[Color] )
    )
    ,'Table2'[Date]
    ,
    ,ASC
    ,DENSE
)

If creating a calculated column you have to be aware that calculated value will not change if for example you apply a filter to the rowset, meaning: if you select May (eg by using a slicer), the value of the column will still show 4 for the color blue on the 26th of May 2017. even if there is now just one row.

Hopefully this is what you are looking for

Regards

Tom

[Bpark1994]

Perfect! Thank you Tom!

[Tadder]

this post may help 🙂

https://community.powerbi.com/t5/Desktop/DAX-Ranking-or-Indexing-Dates/m-p/77794