cancel
Showing results for
Did you mean:
Post Patron

## 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
Resolver II

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
Resolver II

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)
Post Patron

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)

Resolver II

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

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors