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, Day2017-01-02 ,100 €,2017,2017/ene,22017-01-03 ,50 €,2017,2017/ene,32017-01-04 ,75 €,2017,2017/ene,42017-01-05 ,25 €,2017,2017/ene,52017-01-06 ,30 €,2017,2017/ene,62017-01-09 ,40 €,2017,2017/ene,92017-02-01 ,200 €,2017,2017/feb,12017-02-02 ,40 €,2017,2017/feb,22017-02-03 ,50 €,2017,2017/feb,32017-02-05 ,70 €,2017,2017/feb,52017-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
Here it is the PBIX to download https://1drv.ms/u/s!Am7buNMZi-gwjDmsyTY6iSmqtnO1
Go to Solution.
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]))),
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)
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!
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.