Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Here it is the PBIX to download https://1drv.ms/u/s!Am7buNMZi-gwjDmsyTY6iSmqtnO1
Solved! 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]))), Sales[Date],,ASC)
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)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |