Calender table indexing month this month is 0 - in new row so all dates in last month is -1.

[Rygaard]

I get alot of requests, to see data over time, like I want to see last 2 month , last 14 month, last......

up till now i have made that manualy, pr report, but i want a row in the calender table, that index my months

And im pretty sure this can be done with like 3 or 4 lines of code, i simply dont know where to start (either SQL, so i can do it on DW, or how to in PowerBI 🙂 )

All dates in this month is 0

all dates in last month is -1

all dates 2 month ago is -2

.....

all dates 14 month ago is -14

all dates in next month is 1

date (YYYY-MM-DD)	index
2021-12-31	3
2021-10-01	1
2021-09-30	0
2021-09-23	0
2021-09-01	0
2021-08-01	-1
2020-08-01	-13
2020-08-02	-13
2020-08-03	-13

[jppv20 · Accepted solution]

Hi @Rygaard ,

I think this would work. Create a new column in your Calendar Table in PowerBI:

Index = DATEDIFF(TODAY(),'Calendar'[Date],MONTH)

Jori

[Rygaard]

SEE this is why i asked ... i knew someone had some magic up their sleeves !!!!   - TY

[Shishir22]

Hello @Rygaard ,

You can try creating calculated column as:

Index = 
var _today= TODAY()
Var _Diff= DATEDIFF(TODAY(),'Table'[Date],MONTH)
Return
_Diff

 Please mark this as solution if it resolves your issue. Kudos are also appreciated.

[BA_Pete]

Hi @Rygaard ,

*EDIT* simplified answer into single code block.

In Power Query you would do something like this:

Table.AddColumn(
  previousStep,
  "relativeMonth",
  each (Date.Year([date]) * 12 + Date.Month([date])) - (Date.Year(Date.From(DateTime.LocalNow())) * 12 + Date.Month(Date.From(DateTime.LocalNow())))
)

Pete