Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have created a column for last 6 months using below-
But, this gives starting from July to Dec. How can I get it that starts from Current month and goes to 6 months, i.e. December, Nov, Oct, Sept, Aug and July.
Solved! Go to Solution.
Hi @learner03 ,
I have built a data sample by creating a calculated table:
Calendar = ADDCOLUMNS( CALENDAR(DATE(2021,1,1),DATE(2021,12,31)) ,"MonthYear",FORMAT([Date],"mmm-yy") ,"MonthID",MONTH([Date]), "Type","A")
Please try to use RANK() to sort descending:
Rank = RANKX('Calendar',YEAR([Date])*100+MONTH([Date]),,DESC,Dense)
Click MonthYear column --> Sort by Rank:
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @learner03 ,
I have built a data sample by creating a calculated table:
Calendar = ADDCOLUMNS( CALENDAR(DATE(2021,1,1),DATE(2021,12,31)) ,"MonthYear",FORMAT([Date],"mmm-yy") ,"MonthID",MONTH([Date]), "Type","A")
Please try to use RANK() to sort descending:
Rank = RANKX('Calendar',YEAR([Date])*100+MONTH([Date]),,DESC,Dense)
Click MonthYear column --> Sort by Rank:
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |