cancel
Showing results for
Did you mean:
Member

## Rank existing days in month DAX

I have the following data set with sales measure by date, I need to create a new (measure or column) to get the rank of day in month :

This is initial data ( there are gaps in times, not all days have data)

Date, Sales_Amount ,Year, YearMonth, Day
2017-01-02 ,100 €,2017,2017/ene,2
2017-01-03 ,50 €,2017,2017/ene,3
2017-01-04 ,75 €,2017,2017/ene,4
2017-01-05 ,25 €,2017,2017/ene,5
2017-01-06 ,30 €,2017,2017/ene,6
2017-01-09 ,40 €,2017,2017/ene,9
2017-02-01 ,200 €,2017,2017/feb,1
2017-02-02 ,40 €,2017,2017/feb,2
2017-02-03 ,50 €,2017,2017/feb,3
2017-02-05 ,70 €,2017,2017/feb,5
2017-02-06 ,60 €,2017,2017/feb,6

Below I plot what i am looking for with an excel screenshot

I require to get 1 for the first day of month with data, 2 for the second day in a month with data...

I tried with day rank  new column

day Rank = RANKX( ALL(Sales[Date]), Sales[Date],,1)

but i don't know how to rank it by month

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Rank existing days in month DAX

This does the trick for me:

```day Rank = RANKX(FILTER(ALL(Sales),
AND(Sales[Date]>=  DATE(YEAR(EARLIER(Sales[Date])), MONTH(EARLIER(Sales[Date])), 1),
Sales[Date] <=  ENDOFMONTH(Sales[Date]))),
Sales[Date],,ASC)```
3 REPLIES 3
Regular Visitor

## Re: Rank existing days in month DAX

This does the trick for me:

```day Rank = RANKX(FILTER(ALL(Sales),
AND(Sales[Date]>=  DATE(YEAR(EARLIER(Sales[Date])), MONTH(EARLIER(Sales[Date])), 1),
Sales[Date] <=  ENDOFMONTH(Sales[Date]))),
Sales[Date],,ASC)```
Member

## Re: Rank existing days in month DAX

Thank you very much,  and as a final tip for avoiding gaps in grouping measures.

day Rank = RANKX(FILTER(ALL(Sales);
AND(Sales[Date]>=  DATE(  YEAR(EARLIER(Sales[Date])) ; MONTH(EARLIER(Sales[Date])); 1 );
Sales[Date] <=  ENDOFMONTH(Sales[Date])));
Sales[Date];;ASC;Dense)

Regular Visitor

## Re: Rank existing days in month DAX

I had in my original post, but it was not required on the data sample you provided I am glad that It worked out for you!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,175 guests
Recent signins: