Hi
I am trying to write a formula in a DimDate Table, please can soomeone help with the rest of the formula!
The values in the table are:
| Date | Month Name | Month Number | |
| 01/01/2021 | Jan | 1 | |
| 01/02/2021 | Feb | 2 | |
| 01/03/2021 | Mar | 3 | |
| 01/04/2021 | Apr | 4 | |
| 01/05/2021 | May | 5 | |
| 01/06/2021 | Jun | 6 | |
| 01/07/2021 | Jul | 7 | |
| 01/08/2021 | Aug | 8 | |
| 01/09/2021 | Sep | 9 | |
| 01/10/2021 | Oct | 10 | |
| 01/11/2021 | Nov | 11 | |
| 01/12/2021 | Dec | 12 | |
| 01/01/2021 | Jan | 1 | |
| 01/02/2021 | Feb | 2 | |
| 01/03/2021 | Mar | 3 | |
| 01/04/2021 | Apr | 4 | |
| 01/05/2021 | May | 5 | |
| 01/06/2021 | Jun | 6 | |
| 01/07/2021 | Jul | 7 | |
| 01/08/2021 | Aug | 8 | |
| 01/09/2021 | Sep | 9 | |
| 01/10/2021 | Oct | 10 | |
| 01/11/2021 | Nov | 11 | |
| 01/12/2021 | Dec | 12 | |
I am then wanting a new column called [Relative Month Number] where Month 1 is the Month of todays date, Month 2 is the the number of 1st of the month of the preceding date going back based on 12 months.
And, in dates for preceding years the 13th month back would also be 1, 14th month back 2 etc.
So the table would look like this based on todays date of the 22/02/2021
| Date | Month Name | Month Number | Rel Month Number |
| 01/01/2021 | Jan | 1 | 12 |
| 01/02/2021 | Feb | 2 | 1 |
| 01/03/2021 | Mar | 3 | 2 |
| 01/04/2021 | Apr | 4 | 3 |
| 01/05/2021 | May | 5 | 4 |
| 01/06/2021 | Jun | 6 | 5 |
| 01/07/2021 | Jul | 7 | 6 |
| 01/08/2021 | Aug | 8 | 7 |
| 01/09/2021 | Sep | 9 | 8 |
| 01/10/2021 | Oct | 10 | 9 |
| 01/11/2021 | Nov | 11 | 10 |
| 01/12/2021 | Dec | 12 | 11 |
| 01/01/2021 | Jan | 1 | 12 |
| 01/02/2021 | Feb | 2 | 1 |
| 01/03/2021 | Mar | 3 | 2 |
| 01/04/2021 | Apr | 4 | 3 |
| 01/05/2021 | May | 5 | 4 |
| 01/06/2021 | Jun | 6 | 5 |
| 01/07/2021 | Jul | 7 | 6 |
| 01/08/2021 | Aug | 8 | 7 |
| 01/09/2021 | Sep | 9 | 8 |
| 01/10/2021 | Oct | 10 | 9 |
| 01/11/2021 | Nov | 11 | 10 |
| 01/12/2021 | Dec | 12 | 11 |
I start off writing a variable varToday =
today = Date.From(DateTime.LocalNow()),
And then my formula for the Rel Month Number Column begins
Rel Month Number
if
Date.Month([Date]) = Date.Month(varToday)
then 1
else
and then I am struggling with the rest.
Is this the start of a good solution, or is there a better way to look at this?
