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.
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?
Solved! Go to Solution.
without any if then els, letting the date.add month function do the dirty work
Table.AddColumn(#"Aggiunta colonna personalizzata", "relM1", each Date.Month(Date.AddMonths([Date],13 -Date.Month(DateTime.LocalNow()))))
without any if then els, letting the date.add month function do the dirty work
Table.AddColumn(#"Aggiunta colonna personalizzata", "relM1", each Date.Month(Date.AddMonths([Date],13 -Date.Month(DateTime.LocalNow()))))
Many thanks @Anonymous : )
Table.AddColumn(#"Modificato tipo", "relM", each let rmn=-Date.Month(DateTime.LocalNow())+[Month Number]+1 in if rmn>0 then rmn else rmn+12)
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |