Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rygaard
Resolver I
Resolver I

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

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-313
2021-10-011
2021-09-300
2021-09-230
2021-09-010
2021-08-01-1
2020-08-01-13
2020-08-02-13
2020-08-03

-13

1 ACCEPTED SOLUTION
jppv20
Solution Sage
Solution Sage

Hi @Rygaard ,

 

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

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

View solution in original post

4 REPLIES 4
jppv20
Solution Sage
Solution Sage

Hi @Rygaard ,

 

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

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

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

Shishir22
Solution Sage
Solution Sage

Hello @Rygaard ,

 

You can try creating calculated column as:

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

 

Shishir22_0-1632385569022.png

 

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

Cheers,
Shishir
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.