Forum Discussion
Helper VMonth Offset rolling 12 months
Hi,
I have a table that I need a new column to show the months offset on a rolling 12 months. Below is how I would expect to see the table as we're in May
| Month | Month_offset |
| Jan | 9 |
| Feb | 10 |
| Mar | 11 |
| Apr | 12 |
| May | 1 |
| Jun | 2 |
| Jul | 3 |
| Aug | 4 |
| Sep | 5 |
| Oct | 6 |
| Nov | 7 |
| Dec | 8 |
When we move into the June, I would expect the month_offset to shift to to this
| Month | Month_offset |
| Jan | 8 |
| Feb | 9 |
| Mar | 10 |
| Apr | 11 |
| May | 12 |
| Jun | 1 |
| Jul | 2 |
| Aug | 3 |
| Sep | 4 |
| Oct | 5 |
| Nov | 6 |
| Dec | 7 |
Any idea how I can create this Month_offset please?
Hi M_SBS_6
If your current Month column is of Text data type and it only has month short names, you can add a new column with below DAX to have month offset values.
Month_offset = var vdate = DATEVALUE([Month]&" 1 "&YEAR(TODAY())) var curMonth = MONTH(TODAY()) var vMonth = MONTH(vdate) return IF(vMonth<curMonth,vMonth-curMonth+13,vMonth-curMonth+1)Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
1 Reply
v-jingzhangCommunity Support
Hi M_SBS_6
If your current Month column is of Text data type and it only has month short names, you can add a new column with below DAX to have month offset values.
Month_offset = var vdate = DATEVALUE([Month]&" 1 "&YEAR(TODAY())) var curMonth = MONTH(TODAY()) var vMonth = MONTH(vdate) return IF(vMonth<curMonth,vMonth-curMonth+13,vMonth-curMonth+1)Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.