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
ketan10
Resolver I
Resolver I

Dynamic Date Calendar Using Power Query

I need to create a Date column to dynamically update itself daily.


Ex: Using maybe Power Query, I need a column with a list of dates from
(Today-30 days) TO (Today + 90 days).

These dates would change daily.This means that if:
Today = 31-07-2017

Then I need to create a "Dynamic" column which gives me "ALL" the dates between
01-07-2017 (ie Today - 30 days)
--- TO---
29-10-2017 (ie Today + 90 days)

These dates should change dynamically as the Day changes.

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @ketan10,

 

Create the table in the modeling view  the DAX formula will be:

 

Table = CALENDAR(TODAY()-30,TODAY()+90)

Should give the expected result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi @ketan10,

 

Try using this formula:

 

Week_End_Date = DATEADD ( 'Table'[Date], ( 7 - WEEKDAY ( 'Table'[Date], 1 ) ), DAY )

If you need the end of the week be different from Sunday replace the 1 to 2 or 3.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @ketan10

 

Can you tell more about why you need this or what you are trying to achieve with it? And if you insist on using Power Query?

MFelix
Super User
Super User

Hi @ketan10,

 

Create the table in the modeling view  the DAX formula will be:

 

Table = CALENDAR(TODAY()-30,TODAY()+90)

Should give the expected result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks @MFelix this works. But I also need to have a End Of Week column for these dates. Could not find a function for that in DAX.

Hi @ketan10,

 

Try using this formula:

 

Week_End_Date = DATEADD ( 'Table'[Date], ( 7 - WEEKDAY ( 'Table'[Date], 1 ) ), DAY )

If you need the end of the week be different from Sunday replace the 1 to 2 or 3.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Real Deal ! Thanks a lot 🙂 @MFelix

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.