cancel
Showing results for
Did you mean:

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.

Helper I

## [HELP] How to create a Dynamic calendar table from 1st of January until today(-1) for all years

Hi Guys,

I need to create a Dynamic calendar table that will have just the dates from the fist day of the year today(-1) for example today is 25th of October 2018 I would like the table to have all dates from.

1st of January 2015 – 24th of October 2015

1st of January 2016 – 24th of October 2016

1st of January 2017 – 24th of October 2017

1st of January 2018 – 24th of October 2018

I would like to create the same dynamic date table for months. For example, if we are on 25th of October 2018. I would like the dynamic month date table to have all dates between.

1st of October 2015 – 24th of October 2015

1st of October 2016 – 24th of October 2016

1st of October 2017 – 24th of October 2017

1st of October 2018 – 24th of October 2018

I found this code here on https://powerbi.tips/2017/11/creating-a-dax-calendar/ That does almost what I wanted but it creates a table date with all dates from 25th of October 2018 until 25th of October 2015, including for example 26th of October – 31st of December of (2017, 2016, 2015) I needed to exclude that.

Here is the code I am using to create the table.

```Dates 5 =
GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 5, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
)
)```

Unfortunately the Power Query/DAX knowledge that I have are not enough to crack this problem. Any help would be very appreciated.

1 ACCEPTED SOLUTION
Solution Sage

For this scenario I'd rather use Power Query:

Query 1 :

```let
YearsBack = 5,

TodayDate = Date.From( DateTime.LocalNow() ),
YearsList = {0..YearsBack},

Calendar =
List.Accumulate(
YearsList,
#table({},{}),
(state, current) =>
let
FirstDayOfYear = Date.StartOfYear(
),
-1
),
DatesList = List.Transform(
List.Dates(
FirstDayOfYear,
1 + Duration.TotalDays( LastDay - FirstDayOfYear ),
#duration(1, 0, 0, 0)
),
each {_}
),
DateTable = #table( type table [Date = Date.Type], DatesList )
in
Table.Combine( {state, DateTable})
)

in
Calendar```

Query 2:

```let
YearsBack = 5,

TodayDate = Date.From( DateTime.LocalNow() ),
YearsList = {0..YearsBack},

Calendar =
List.Accumulate(
YearsList,
#table({},{}),
(state, current) =>
let
FirstDayOfYear = Date.StartOfMonth(
),
-1
),
DatesList = List.Transform(
List.Dates(
FirstDayOfYear,
1 + Duration.TotalDays( LastDay - FirstDayOfYear ),
#duration(1, 0, 0, 0)
),
each {_}
),
DateTable = #table( type table [Date = Date.Type], DatesList )
in
Table.Combine( {state, DateTable})
)

in
Calendar```

Proud to be a Datanaut!

6 REPLIES 6
Solution Sage

For this scenario I'd rather use Power Query:

Query 1 :

```let
YearsBack = 5,

TodayDate = Date.From( DateTime.LocalNow() ),
YearsList = {0..YearsBack},

Calendar =
List.Accumulate(
YearsList,
#table({},{}),
(state, current) =>
let
FirstDayOfYear = Date.StartOfYear(
),
-1
),
DatesList = List.Transform(
List.Dates(
FirstDayOfYear,
1 + Duration.TotalDays( LastDay - FirstDayOfYear ),
#duration(1, 0, 0, 0)
),
each {_}
),
DateTable = #table( type table [Date = Date.Type], DatesList )
in
Table.Combine( {state, DateTable})
)

in
Calendar```

Query 2:

```let
YearsBack = 5,

TodayDate = Date.From( DateTime.LocalNow() ),
YearsList = {0..YearsBack},

Calendar =
List.Accumulate(
YearsList,
#table({},{}),
(state, current) =>
let
FirstDayOfYear = Date.StartOfMonth(
),
-1
),
DatesList = List.Transform(
List.Dates(
FirstDayOfYear,
1 + Duration.TotalDays( LastDay - FirstDayOfYear ),
#duration(1, 0, 0, 0)
),
each {_}
),
DateTable = #table( type table [Date = Date.Type], DatesList )
in
Table.Combine( {state, DateTable})
)

in
Calendar```

Proud to be a Datanaut!

Frequent Visitor

Thanks for your solution it is really helped - One more thing:

If I need to a previous year full dates how I can do that.

example: 2019 UTD 6-Aug and 2018 UTD 6-AUG  --- I need  2019 UTD 6-AUG and 2018 full days

Helper I

Hi Livio,

I just saw this post and I was wondering if there was a way to rewrite this formula to show 12 months back from current date instead of years. I tried replacing the "Year" with "Month" but I received an error message. I have been working on a query for a while and your formula is the closest that I have found to creating the query that I need.

Any help would be appreciated. Thank you in advance.

Regards,

Angela

Helper I

It worked you are a genius!!!!!

That’s perfect to do YTD and MTD measures. Because I was having trouble with TOTALYTD measure because all it does is accumulate the months but I did not want to get the whole month, so figured I had to create a new date data set, and it worked!!!

Thank you once again!

Could you point the way what are the PowerQuery commands to do the same as I was doing before?

Because the only output I get from the query that you´ve made are the dates, but it would be nice to also have

VAR days = DAY( currentDay )

VAR months = MONTH ( currentDay )

VAR years = YEAR ( currentDay )

VAR nowYear = YEAR( TODAY() )

VAR nowMonth = MONTH( TODAY() )

VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

VAR todayNum = WEEKDAY( TODAY() )

VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

RETURN ROW (

But I guess I can create this on power query editor the extra columns…

Regards,

Tiago

Solution Sage

Hi @tiago

You can add extra steps to the query via the UI. Just go to the Add Column tab and go to the From Date & Time section

Proud to be a Datanaut!

New Member

I have similar problem, would love some input on this! Thx for elaborating the question.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors